Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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

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) 
)
)

 

 

APIS and Embedding
Reports
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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 )
        )
    )

8 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    Anonymous

     

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

     

     

    Best Regards,

    Herbert

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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])
			)
		)
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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:

       

      Ideally, the output shoudl look like this:

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        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 )
        )
    )