Power BI - Get the sum of the Last/Latest Value of a Category/Group 07-20-2023 05:55 AM

[amitchandak]

Power BI - Get the sum of the Last/Latest Value of a Category/Group

Problem Statement:

In the given data filter and show only the most recent row based on date/version etc. It should also do the correct sum. 

In the last blog, I discussed how can we get the latest status, but it will not give the correct grand total for qty/value. 
Data: I have taken data where we have ID and date along with Qty. And we would like to show the latest Qty.

Solution:

We will use a DAX measure for that.

I have loaded the data to the power bi file and created the following DAX measure
DAX Code- I created 2 measures

Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[ID] = max(Data1[ID]) ),Data1[Date])
return 
CALCULATE(sum(Data1[qty]), filter( (Data1), Data1[ID] = max(Data1[ID])  && Data1[Date] =_max))

Sum Last Qty = sumx(VALUES(Data1[ID]) , [Last Qty])

Please check more details on the blog

[LoganH244]

Hello,

I am trying to build a matrix in PowerBI and have created a new measure for my value field: 

PSUs = CALCULATE(DISTINCTCOUNT(ARPU[DistinctNumber])) to count distinct values, but the subtotals are not correct with this method. 

Do you have any suggestions?