Running total based on RANKX

[Anonymous]

Hi all,

- File link here -

I have a dynamic RANKX() measure that will recalculate based on a filter context (in this case - Month Year & Sales district)

I am trying to create a running total measure based on this RANKX().

It is partially working as you can see in the image below, but for some reason the running total starts on the second row and the final row does not equal the total of Quantity (t) column (13,707.79).

Here are my measures :

- SumPrice = sum(Test[Price (€)])

- Price (€/t) = [SumPrice]*1000/[Quantity (t)]

- RankX = RANKX(ALLSELECTED(Division[Product code]),CALCULATE(([Price (€/t)])),,ASC,Dense)

- Running total sales =

VAR IndexRank = [RankX]

RETURN

CALCULATE (

SUM(Test[Qty (t)]),

FILTER ( ALL ( Division[Product code] ),

IndexRank

>= RANKX ( ALLSELECTED ( Division[Product code] ), [Price (€/t)],, ASC, Dense )

)

)

Sorry for code sample not inserted in a proper way but editing window won't let me put it.

Thanks for the help, 

[Anonymous]

Thanks a lot @lbendlin, this is what I was missing.

Concerning the HASONEVALUE, I'm not familiar with it, do I need to create a new measure or include it within my measures?

Thanks,

[lbendlin]

You include it in the measure logic to distinguish between individual cells and the row total location of your visual.

[lbendlin · Accepted solution]

change 

FILTER ( ALL ( Division[Product code] ),

to 

FILTER ( ALLSELECTED ( Division[Product code] ),

Note that your totals are still wrong - you need to use HASONEVALUE  to handle that case separately.