Running total to sum a measure

[PivotRiot]

Hi, 

I have started a topic here already but haven't checked properly the answer. It doesn't work for me because I want a running total of a measure [SalesTotal] which is a price of previous year multiplied by qty sold this year, not a column [Sales]. The SUM function doesn't support summing of measures, only existing columns of the table. So I have to put the measure itself to calculate without SUM function. But if I use the following code:

= CALCULATE(Data[SalesTotal]; DATESYTD('Calendar'[Date]; "31/12"))

instead of 

= CALCULATE(SUM(Data[Sales]); DATESYTD('Calendar'[Date]; "31/12"))

I get blank cells as a result.

@Ashish_Mathur 

May I ask your suport once again?

The sample file is by the same link.

Thank you.

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[Ashish_Mathur]

Hi,

What is the mistake in the numbers which you show in the Excel file?  In that same file, show the correct numbers.  Share the link of the revised file.

[PivotRiot]

Hello, I am afraid that the solution you propose does not resolve my problem. You simply propose running total not taking into account that it should calculate row by row first and only then make a running total. 

I am pretty sure that the code I use is almost acceptable for me, but doesn't work well for those items which only has a record in February in this particular example. 

So they doesn't add up correctly. 

In your file for product A+B in year 2024 the running total is following:

Jan 1 944 655,68

Feb 3 141 570,12

Mar 6 100 828,77

But it should be the following:

Jan 1 717 254

Feb 5 071 157

Mar 7 684 370

With my code

= SUMX(
VALUES(Data[Product]);
	CALCULATE(Data[SalesTotal]; FILTER(ALLSELECTED('Calendar'[Date]); 'Calendar'[Date] <= MAX(Data[SalesDate]))))

I can only have the following result:

Jan 1 717 254 (only the value of product A, because no sales of product B in Jan)

Feb 3 353 903 (only the value of product B, not adding product A, which had no sales in Feb)

Mar 4 330 467 (only cumulative of proudct A for the months Jan and Mar not counting product B, which had sales only in Feb)

Total 7 684 370 (adding both product A and B correctly).

So I am still stuck on this. Could anyone help?

[Rebellionaire]

Hi Ashish, 

the link to the file is correct.

https://www.dropbox.com/scl/fi/9r05a38rzwhgqv25ycjoo/Demo1.xlsx?rlkey=81qxdzeys6a8r2ittezshufpe&dl=0

The result @PivotRiot wants is cumulative running total for the measure of multiplication of the average price 2023 for the quantity 2024. Use the slicer to filter the product and try to get the expected result with the DAX code in the attached file or propose yours: 

 

 

 

[PivotRiot]

Correct.

[Greg_Deckler]

@PivotRiot This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Rebellionaire]

Hi @Greg_Deckler 

thank you for your input. What would be the pattern for the running total?

[Greg_Deckler]

@Rebellionaire Better Running Total - Microsoft Fabric Community