cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbiBXL1234
Helper I
Helper I

Running total based on ranking index

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 :
 
MonthYearProduct codeSales (t)Price (€/t)RankRunning total sales
01-05-194973883233,9167,81 
01-05-194974072486,1170,22 
01-05-194974011076,9171,43 
01-05-194974191044,1179,34 
01-05-195063241650,6198,45 
01-05-19497404747,5200,16 
01-05-194974666374,4203,77 
01-05-19497437762,5270,68 
01-05-19501755840,7272,39 
01-05-19497413124,3275,410 
01-05-195021051274,7355,511 
01-05-19497457203,7629,712 

 

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!

 

6 REPLIES 6
AntrikshSharma
Super User II
Super User II

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

1.png

2.png

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
Super User I
Super User I

Hi @pbiBXL1234 ,

Could it be you typed > instead of >=?

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Payeras_BI
Super User I
Super User I

Hi @pbiBXL1234 ,

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

Payeras_BI_1-1614002352082.png

 

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

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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:

pbiBXL1234_0-1614003375900.png

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
Super User IV
Super User IV

@pbiBXL1234 , 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] )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors