Calculate sum of unit prices for latest sale, by segment

[ali_b]

(Editted to have correct requirements)

Hello,

I'm trying to create a DAX measure to calculate the unit price (sales amount / unit) of the last Sale in a Segment (i.e. the biggest Sales ID).

I'm using the "Sales & Returns sample dataset" sample dataset from Power BI and DAX Studios to debug my measure.

From this table below of Sales[ID], Product[Segment],Sales[Amount],Sales[Unit], I want my measure to first get a list of all the latest sales for each segment (highlighted in yellow), and then sum all their amounts, sum all their units, and divide the total amounts by the total units.

So in the example table below, the measure should return: (52+84+85+40+38+45+196+100+255) / (2+3+1+1+1+1+2+2+3) = 895/16 =55.4

I'm stuck on how to use a calculatedtable in calculate. Here is where I've gotten to in DAX studios:

The summarize function works, but the calculate table doesnt: it still returns a lot of IDs.

Unfortunately the above is also throwing the error: 

"Table variable 'f' cannot be used in current context because a base table is expected."

I've tried a lot of different options with crossjoin, etc. but nothing seems to work!

Here are the relevant parts of the datamodel:

Any help would be greatly appreciated 🙂

[tamerj1]

Hi @ali_b 

please try

=
SUMX (
TOPN ( 1, SUMMARIZE ( Sales, Sales[ID], Product[Segment] ), Sales[ID] ),
CALCULATE ( DIVIDE ( SUM ( Sales[Amount] ), SUM ( Sales[Unit] ) ) )
)

[ali_b]

Thanks @tamerj1 , unfortunately that isn't quite doing the right thing. 

From the diagram below, if I try to aggregate up to another dimension like Category, you can see that it's correctly determining the latest sale per Segment (blue pen), but it's not aggregating all the segments up correctly to the Category (red pen).

I noticed I hadn't specified the aggregation correctly in my post above and hence have editted it.

I've tried to have a look at why that measure isn't working quite as expected but no luck yet.