Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PivotRiot
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
Greg_Deckler
Super User
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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

PivotRiot_0-1714587463383.png

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

PivotRiot_1-1714588265897.png

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

 

@Greg_Deckler Thanks for your contribution on this thread.

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

 

In addition, you can refer the following links to try to solve your problem...

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.

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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