2 Replies Latest reply on Oct 18, 2016 7:02 AM by Frank-Michael Idrees

    Calculation: Consindering the latest record only

    Frank-Michael Idrees

      Hi Tableau Community,

       

      Apologize first for the title but I don't know how this kind of issue is called.

       

      So here the problem:

       

      I have these two simple tables (Sales Orders and Purchase Orders), the link between these tables is the Inventory ID:

      Screen Shot 2016-10-10 at 7.10.39 PM.png

       

      First of all I want to count the number of Sales Orders. If I use just the default number of records it wil show 14 but with the use of CountD function on SO IDs  I'm getting the correct 10 Sales Orders. This part is easy but when it comes to calulate the Margin (which is basically the Sales Amount minus the related Purchase Amount) it will consider all related POs. But in my case I just want to consider (in case there are multiple POs related) the latest PO (=the latest PO ID) for the Margin Calculation. So for example: SO ID 1 (Inventory ID: 10) has a sales amount of $1,100 and there are 3 related PO IDs (10/20/120) with different 3 purchase amounts (1,500/1,400/1,000). I want only to consider ther MAX PO id (120) for the margin calculation which hast the Purchase Amount of 1,000 and the correct Margin of 1,100-1000=100.

       

      Actually I tried it with some not very efficient workarounds (Index/Filters etc.) but there must be a much better way. It would also help if someone knows how this kind of problem actually is called, I'm sure this is a basic issue when dealing with databases.

       

      Thank you very much in advance.