Running total with ranking

[chsardas]

Hi,

I'm trying to do a Running Total based on a rank of a measure.

After a little of research i've found the way to do the running total but i'm having a trouble with that.

I create a rank measure:

Ranking City = 
RANKX(ALL(Sales[City]),[QTR Quantity],,DESC,Skip)

And a measure for the running total:

Running Total = 
CALCULATE (
[QTR Quantity],
( TOPN ( [Ranking City], ALL ( Sales[City] ), [QTR Quantity] ) ))

The problem is when a pair of cities have the same rank (a tie situation) the running total sum the value of both cities in the same row / point (when it's a chart).

Example:

I think the solution is try to change the ranking so when there's a tie it should put different ranking for each member.

Thanks!

[Zubair_Muhammad]

Hi @chsardas

Could you try this?

Running Total =
CALCULATE (
    [QTR Quantity],
    (
        TOPN (
            [Ranking City],
            ALL ( Sales[City] ),
            [QTR Quantity]
                + RANDBETWEEN ( 1, 100 ) / 1000
        )
    )
)

[chsardas]

Thanks for your reply!.

Now it only sum one of the members which are tied.

But the behavior i expect is cardenas shoud be 11.511 + 515 and COMALCALCO should be 12.026 + 515. 

[Zubair_Muhammad]

HI @chsardas

Instead of Running_Total try doing this for Ranking City

The idea is to differentiate between 2 similar Ranks by decimal points. You can hide the decimals for RANKING

Ranking City =
RANKX ( ALL ( Sales[City] ), [QTR Quantity],, DESC, SKIP )
    + RANDBETWEEN ( 1, 100 ) / 1000

[chsardas]

Nice idea, now the ranking shows different values:

But the running total keeps returning the same, it looks like the top n doesn't take care of the decimal. 😞

[Zubair_Muhammad]

HI @chsardas

Sorry I went to sleep.

Could you try this please?

I hope it will work

Ranking City =
RANKX (
    ALL ( Sales[City] ),
    [QTR Quantity]
        + RANDBETWEEN ( 1, 100 ) / 1000,
    ,
    DESC,
    SKIP
)

[chsardas]

Hi!! Now the ranking works better but the running total is really weird:

[Zubair_Muhammad]

Could you share your file via one drive or googledrive?

[chsardas]

I've sent you a PM. 

[chsardas · Accepted solution]

Finnally i´ve got the solution.

I´ve changed the approach of the formula.

Used this guide (it´s in spanish)

https://www.pbiusergroup.com/blogs/victor-velarde/2017/07/07/como-realizar-un-diagrama-8020-o-de-dis...