Forum Discussion

MWinter225's avatar
MWinter225
Advocate IV
9 years ago
Solved

Cumulative SUM using Rank, NOT Dates

Hi All,   I've been researching a while about how to do this and I feel like I'm getting close. I'm pretty familiar with DAX but feel much more comfortable in languages used by Tableau and Spotfire...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi MWinter225,

    In your scenario, you can create a rank column instead of measure using DAX below.

    RankColumn = RANKX(FILTER('Rank Report',NOT(ISBLANK('Rank Report'[MER]))),'Rank Report'[MER],,0,Dense)

     

    Then, create the following measures.

    SumCost = SUM('Rank Report'[Media Cost])
    Cumulative Cost = CALCULATE([SumCost],FILTER(ALL('Rank Report'),'Rank Report'[RankColumn]<=MAX('Rank Report'[RankColumn])))

     

    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi MWinter225,

In your scenario, you can create a rank column instead of measure using DAX below.

RankColumn = RANKX(FILTER('Rank Report',NOT(ISBLANK('Rank Report'[MER]))),'Rank Report'[MER],,0,Dense)

 

Then, create the following measures.

SumCost = SUM('Rank Report'[Media Cost])
Cumulative Cost = CALCULATE([SumCost],FILTER(ALL('Rank Report'),'Rank Report'[RankColumn]<=MAX('Rank Report'[RankColumn])))

 

Thanks,
Lydia Zhang

vanessa's avatar
vanessa
Post Patron
8 years ago

Hi Anonymous

 

I am trying to implement something similar. I want to calculate the running sum for product sub-categories based on sales, but I have a slicer for product categories which targets a grid with data for Product sub-category and sales. The solution mentioned by you works if I have a single product category selected in the slicer. If i "Select All", the rank and cumulative sum doesn't get calculated correctly.