Running total based on ranking index

[Anonymous]

Hi all! 

I'm looking for a way to calculate a Running total. So from what I read before, I tried to create a Ranking index with this formula:

Rank = RANKX(ALLSELECTED(Division[Product code]),CALCULATE(([Price (€/kg)])),,ASC,Dense)

And that works pretty well because I have the result on the table here :

MonthYear	Product code	Sales (t)	Price (€/t)	Rank	Running total sales
01-05-19	497388	3233,9	167,8	1
01-05-19	497407	2486,1	170,2	2
01-05-19	497401	1076,9	171,4	3
01-05-19	497419	1044,1	179,3	4
01-05-19	506324	1650,6	198,4	5
01-05-19	497404	747,5	200,1	6
01-05-19	497466	6374,4	203,7	7
01-05-19	497437	762,5	270,6	8
01-05-19	501755	840,7	272,3	9
01-05-19	497413	124,3	275,4	10
01-05-19	502105	1274,7	355,5	11
01-05-19	497457	203,7	629,7	12

But then when I try my formula for the Running Total Sales :

Running total sales = 
Var IndexRank  = [Rank]

Return 
SUMX( FILTER(
    SUMMARIZE( Division, Division[Product code],
    "Sales" , Sales[Sales (kg)],
    "Sales Ranking" , RANKX( ALL(Division) , [Sales (kg)], , ASC) ), 
        [Sales Ranking] <= IndexRank ),
        [Sales] )

It just shows nothing as displayed on the table above ...

So basically what I would like to see is the running total of the Sales column based on the Rank which is related to the column  [Price (€/t)].

Does someone see the problem here?

Thanks a lot for your help!

[AntrikshSharma]

@Anonymous  Is this what you want to achieve? PBI file is uploaded below my signature.

Rank = 
RANKX (
    SUMMARIZE (
        ALLSELECTED ( Division ),
        Division[MonthYear],
        Division[Product code]
    ),
    CALCULATE ( SUM ( Division[Price (€/t)] ) ),
    ,
    ASC,
    DENSE
)

Running total sales = 
IF (
    ISINSCOPE ( Division[Product code] ) && ISINSCOPE ( Division[MonthYear] ),
    VAR CurrentRank = [Rank]
    VAR SummarizeDivision =
        CALCULATETABLE (
            SUMMARIZE ( Division, Division[Product code], Division[MonthYear] ),
            ALLSELECTED ( Division )
        )
    VAR DivisionSales =
        ADDCOLUMNS ( SummarizeDivision, "@Sales", [Total Sales], "@Rank", [Rank] )
    VAR Result =
        SUMX ( FILTER ( DivisionSales, [@Rank] <= CurrentRank ), [@Sales] )
    RETURN
        Result
)

[Payeras_BI]

Hi @Anonymous ,

Could it be you typed > instead of >=?

[Payeras_BI]

Hi @Anonymous ,

If I understood correctly your requirement you are looking for this:

Running total sales =
VAR IndexRank = [Rank]
RETURN
    CALCULATE (
        SUM ( Division[Sales (t)] ),
        FILTER (
            ALL ( Division[Product code] ),
            IndexRank
                >= RANKX ( ALLSELECTED ( Division[Product code] ), [sum price],, ASC, DENSE )
        )
    )

[Anonymous]

Hi @Payeras_BI ,

Indeed, you understood correctly as it displays what I am looking for but there is one problem and I don't understand where it comes from: there is a row offset as you can see on the screenshot below:

Also I don't know if this is useful but the column "Price (€/t)" is also a measure.

Do you know where this might come from?

Thanks again,

[amitchandak]

@Anonymous , Try a new measure like

SUMX( ADDCOLUMNS(
SUMMARIZE( Division, Division[Product code],
"Sales" , Sales[Sales (kg)],
"Sales Ranking" , RANKX( ALL(Division) , [Sales (kg)], , ASC) ),
"Cumm" , calculate(sum([Sales]), filter( Division, [Sales Ranking] <= max([Sales Ranking]) ))),
[Cumm] )

[Anonymous]

Hi @amitchandak ,

Thanks for the quick reply.

It is not working as I need my Rank to be a measure and not a column because I have a lot of datas (the table here is just a filtered example of my datas) and I need this ranking to be dynamic as I am filtering on date, types of products and many other fields so the only way of doing this is with a measure and Max function only accepts a column reference.

Do you know another way of doing this with Ranking being a measure ?

Thanksss