cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Running total in Power Pivot based on other measure with missing values in certain months

Hi there,

I am sorry to address with the same question for the 3rd time, but it seems I can't explain what I need in a proper way.

I have tried to make a running total based on a measure, see the file by the link.

My problem is that when I add up the items which has only sales in the middle month, here in February, it doesn't show the running total in a correct way.

@Ashish_Mathur proposed a solution in his file, but this doesn't add up by product by month using a measure called [Sales Total] which is a quantity sold in 2024 multiplied by the average price of 2023, so for products A+B in year 2024 the running total is shown incorrectly:

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?

These are the two topics I have already started but couldn't get the acceptable solution: one, two.

My other problem is that I need to use PowerPivot, not a PowerBI and it seems that some code proposed for PowerBI doesn't show correct results for Power Pivot.

5 REPLIES 5
Super User

@PivotRiot Maybe:

``````Measure =
VAR __Date = MAX( 'Date'[SalesDate] )
VAR __Table = SUMMARIZE( FILTER( 'Data', [Date] <= __Date ), [Product], [Date], "__Value", [SalesTotal] )
VAR __Result = SUMX( __Table, [__Value] )
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hi Greg, thanks for your quick response. I tried this in PowerPivot but only get a blank cells. May I ask you please for PowerPivot solution or modify and attach my original xls file?

Super User

@PivotRiot I got what appears to be a running total. Had to rewire a bunch of underlying measures. See attached below signature.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hi Greg,

not sure, I get what solution do you propose in the attached file. What modification to the measures [RT Sales1] or [RT Sales2] can you propose to make it look like in the column *What I need:* I have added manually.

The [AvgPrice 2023] and the [RT Sales1] measures are calculated first by product A and B in separate pivot table.

So [RT Sales1] works correctly by product, but doesn't work by product by month together.

Community Support

Hi @PivotRiot ,

Did you get the solution? If no, could you please provide some sample data (exclude sensitive data) with Text format and the calculation logic of measure [AvgPrice 2023], [RT Sales1] and [What I need] with the speicial examples and screenshot? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors