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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

       

      Thank you!

    • KMcCarthy9's avatar
      KMcCarthy9
      Helper V
      5 years ago

      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?