DAX: Filtering table to latest transactions, then aggregating

[ali_b]

I'm stumped. 

I'm trying to create a report with filters on pretty much every concievable dimension, a list of all sale transactions over time, and a table which shows a measure, grouped by Segment and then Category.

My challenge is that I can't seem to create the measure in DAX because it has to do two operations one after the other, where both operations are dependent on the FULL filter context from the report's slicers, etc.  Generally, the operations the measure should do is:

1) Get all the latest Sales transactions per Segment, and per Category (in the current filter context)

2) Sum all the latest transaction amounts

3) Sum all the latest transaction units

4) Divide latest transaction amounts total with the transaction units total to give some sort of average amount per unit for all the latest transactions per Category and Segment.

If the process didn't need to take into account a dynamic filter context from slicers, then I could simply do step 1 in SQL / Power Query, but I can't: hence I need a DAX calculation.

If the process just needed to work on a single latest transaction, then I wouldn't need step 1;   and steps 2 and 3 are easily to achieve for me in DAX (get latest sales ID, filter sales table by latest sales ID, sum amount, sum units, divide the two... easy), but I can't: the requirement is to filter down to ALL the latest transactions per segment and category AND THEN aggregate and divide.

My attempt uses the standard Sales & Returns sample dataset and can be found here: LatestDataIssue - Sales & Returns Sample v201912.pbix

Here is where I got to, and what I need help with:

A) Various slicers that change the data over which everything should be calculated
B) A full list of sales transactions from the Sales table, ordered by Sales[Date] descending, and then Sales[ID] descending (see C), which shows the "latest" sales transaction with the largest ID.
D) A product slicer which is removing the latest sales transaction off the top of the table (on purpose: testing the measure are filter full context sensitive) 

E) Two measure (Latest Sales Amount Largest ID, Latest Sales Units Largest ID), which I've implemented correctly, but its NOT what I want:

     Latest Sales Amount (Largest ID):  finds the amount of the latest transaction (correctly finds Amount to be 84)

     Latest Sales Units (Largest ID):  finds the units of the latest transaction (correctly finds Units to be 3)

     Latest Unit Sales Amount (Largest ID):  divides the top measure by the second measure (correctly divides 84/3=28) see F

G) Here is the table where it's easy to see that this is not doing what I want it to

    G1: You can see that the "Office 365" row aggregates to Latest Sales Amount = 84, Latest Sales Units = 3, and hence Latests Sales Units Amount = 28 (same as E & F above, because the latest sales transaction happens to be within Office 365 Category). This is not what I want however, because it is identical to G2: the Segment (Purple) for the latest transaction in the whole Category.

Instead, I want the following for Category="Office 365":

1) LSA to be the SUM of all latest amounts in the category, so: 38 (Blue)+45 (Cyan)+100 (Jade... etc.)+196+160+84+100+40 = 847

2) LSU to be the SUM of all latest units in the category, so: 1+1+2+2+2+3+2+1= 14

3) LSA/LSU = 847 / 14 = 60.5

I've tried to do the following in the measure, but hit various errors (see comments):

TRYING - Latest Sale Amounts (largest Id) =
    -- Group combined table to get latest sales transactions per product category and segment
    var latest_transactions = SUMMARIZE('Product', [Category], [Segment], "Sales ID", max(Sales[ID]))

    -- calculate the sum of sales amount for all sales transactions in the latest list
    --var agg = CALCULATE(sum(Sales[Amount]), Sales[ID] in latest_transactions ) -- Error: The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression.
    --var agg = CALCULATE(sum(Sales[Amount]), Sales[ID] in latest_transactions[Sales ID] ) -- Error: Cannot find table 'latest_transactions'.
    --var s = values(latest_transactions[Sales ID]) var agg = CALCULATE(sum(Sales[Amount]), Sales[ID] in s ) -- Error: Cannot find table 'latest_transactions'.
    var agg = CALCULATE(sum(Sales[Amount]), Sales[ID] in {latest_transactions[Sales ID]} ) -- Error: Cannot find table 'latest_transactions'.
   
    -- calculate the sum of sales amount for all sales transactions in the latest list
    return agg

I've tried numerous other ways to solve this. Too many to write down here... including looking at these pages:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-reference-columns-in-a-summar...

Help!

[some_bih]

Hi @ali_b in short your issue is part G? Everything else is ok from picture you provide? 

If yes, you just sum correctly per Category? If yes, what is measure you used in part G?

[ali_b]

Thanks @some_bih - yes: part G is the problem. Everything else in the picture is okay. The picture shows the outcome of the following measures, but as I said, this is not yielding the correct values and it's probably clearer to look at the measure above that DIDNT work.

Latest Sale Id = 
-- gets a single latest Sale ID in current filter context (I want it to return the latest SaleIDs for all Categories & Segments):
var latest_date = max(Sales[Date])
var latest_value = CALCULATE( max(Sales[ID]), REMOVEFILTERS(Sales[Date]), FILTER(Sales, Sales[Date] = latest_date))
return latest_value

Latest Sale Amount (Largest Id) = 
var latest_id = [Latest Sale Id]
var latest_value = CALCULATE( sum(Sales[Amount]), FILTER(Sales, Sales[ID] = latest_id))
return latest_value

Latest Sale Units (Largest Id) = 
-- Get the latest sale id
var latest_id = [Latest Sale Id]
var latest_value = CALCULATE( sum(Sales[Unit]), FILTER(Sales, Sales[ID] = latest_id))
return latest_value

Latest Unit Sale Amount (Largest Id) = DIVIDE([Latest Sale Amount (Largest Id)], [Latest Sale Units (Largest Id)])