cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamRock Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: Quick Measure - Running Total Sorting issue

@SamRock

 

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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




5 REPLIES 5
v-haibl-msft Super Contributor
Super Contributor

Re: Quick Measure - Running Total Sorting issue

@SamRock

 

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

Super User
Super User

Re: Quick Measure - Running Total Sorting issue

Hi @SamRock

 

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!

SamRock Frequent Visitor
Frequent Visitor

Re: Quick Measure - Running Total Sorting issue

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

Super User
Super User

Re: Quick Measure - Running Total Sorting issue

@SamRock

 

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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Olebrumm Frequent Visitor
Frequent Visitor

Re: Quick Measure - Running Total Sorting issue

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

 

Thank you!