Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Quick Measure - Running Total Sorting issue

 

I am trying to create a Pareto Chart which shows the running Total of Spend in percentge as Line.

The Columns are sorted by Total Spend for each category (MM, PPP, SS etc) in Desc order

runningtotal.png

I used Quick Measure option on Sum("Spend") field to create a "Running Total". When this is added as a Line Value, the data points are appearing sorted by the Category ( notice VVV has 100% and its the 4 bar, ).

The Cumulative of Spend (Running Total) should happened by the Total Spend sorted in Desc order, not by Category Text!
Here is the DAX created by Quick Measure:

Sumof Line Amount running total in Category 

= CALCULATE( 

SUM('Spend'[Line Amount]), 

FILTER(  
ALLSELECTED('Spend'[Category]),  

ISONORAFTER('Spend'[Category], 
MAX('Spend'[Category]), 
DESC) 
)
)

 

 

1 ACCEPTED SOLUTION

@Anonymous

 

Hi, Try with this Code: (Assuming that the Graph was sorted by Spend)

 

Spend running total =
VAR spend =
    CALCULATE ( SUM ( Table2[Spend] ) )
RETURN
    CALCULATE (
        SUM ( Table2[Spend] ),
        FILTER (
            ALL ( Table2[Category] ),
            CALCULATE ( SUM ( Table2[Spend] ) >= spend )
        )
    )

Pareto.png




Lima - Peru

View solution in original post

8 REPLIES 8
Phil_Seamark
Microsoft Employee
Microsoft Employee

Hi @Anonymous

 

Does this measure get close to what you need?

 

Sumof Line Amount running total in Category = 
	CALCULATE(
		SUM('Spend'[Line Amount]),
		FILTER(
			ALL('Spend'),
			'Spend'[Category]<=MAX('Spend'[Category])
			)
		)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @v-haibl-msft@Phil_Seamark

 

Thank you for the resposes!

 

1) Sorting on the line will not working, because its a Pareto Chart, that should Columns sorted by Top Spend. The Line should have Running total for each Bar.

 

 

2) Phil, I tried your Formula, its giving similar output. Here is the output:runningtotal2.png

 

Ideally, the output shoudl look like this:

runningtotal2.png

@Anonymous

 

Hi, Try with this Code: (Assuming that the Graph was sorted by Spend)

 

Spend running total =
VAR spend =
    CALCULATE ( SUM ( Table2[Spend] ) )
RETURN
    CALCULATE (
        SUM ( Table2[Spend] ),
        FILTER (
            ALL ( Table2[Category] ),
            CALCULATE ( SUM ( Table2[Spend] ) >= spend )
        )
    )

Pareto.png




Lima - Peru

Hi, if the spend for some of the categories is exactly the same (the column spend has duplicate values), then this formula is not useful as it shows the same Running Total for the duplicate values. Do you have any suggestion how to treat this issue? Thanks.

Hi,

Yes, there is a way to solve for that.  Share some data and also the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I just found this thread and it solved my problem! However, would you know why I am not getting a total? The initial Cumulative Spend column was working but it was based on an alphabetical sort, where I wanted it based on descending spend sort, which your post did. However my first one gave me a total and the new one is not. Any ideas? 

 

pic2.JPG


Anonymous
Not applicable

Yes! This worked for me. After two days of googling, I finally found this solution.

 

Thank you!

v-haibl-msft
Microsoft Employee
Microsoft Employee

@Anonymous

 

You can change the sort order as below. Not sure if it is your expected result.

 

Quick Measure - Running Total Sorting issue_1.jpg

 

Best Regards,

Herbert

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.