Forum Discussion

cwermager's avatar
cwermager
Frequent Visitor
5 years ago

Running Total Based on Rank with Filters

Hi all.

 

I've scoured the forums for days to no avail. I'm no DAX expert, but have tried seemingly infinte variations of the proposed solutions. I'm trying to calculate a running total based on a rank using measures so I can filter by area. The example file in this post seemed most promising, but I still couldn't get it to work and it would only return the inidividual cost and not the running sum.

 

I have been able to get a Rank measure to work when filtering on one or multiple areas, but have stalled out on the running total.

 

RANK  = RANKX(ALLSELECTED(rv_WETS_LatestCache) , CALCULATE( DIVIDE(SUM(rv_WETS_LatestCache[OneYearBenefit]),sum(rv_WETS_LatestCache[JobCost]))),,DESC,Skip)

 

Here is some sample data. Each area has a variety of jobs that have a cost and benefit associated with them. The rank is calculated based on the efficiency, benefit/cost. I am trying to tally a cumulative cost based on the rank.

 

AreaJobIDCostBenefitEfficiencyRankCumulative Cost
A30010001500015.011000
A304150080005.345200
A30812001200010.022200
B3033000-4000-1.3915700
B301250050002.0812700
C3051500140009.333700
C306120050004.256400
C307200060003.068400
C302180040002.2710200

 

When filtering to a specific area I am trying to get the rank and cumulative cost to dynamically adjust. This is what I would expect:

 

AreaJobIDCostBenefitEfficiencyRankCumulative Cost
C3051500140009.311500
C306120050004.222700
C307200060003.034700
C302180040002.246500

 

Any help would be greatly appreciated.

 

Thanks.

Chad

Need Help

3 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    5 years ago

    I would not start such a complex task without a dimensional model. I suggest you creat a dimension table for the area and join it to your current table. Then use the new area column in your slicer and visual, and also change the reference in the measure. I don't know if that will fix it, but it is where I suggest you start. 

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi cwermager 

    I think you can do it like this:

     

     

    Division = DIVIDE(MIN('Table'[Benefit]),MIN('Table'[Cost]))

Rank = RANKX(ALLEXCEPT('Table','Table'[Area]),[Division],,DESC,Skip)

     

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

    • cwermager's avatar
      cwermager
      Frequent Visitor
      5 years ago

      Thanks FrankAT. The rank is actually the one part that seems to be working. I am struggling to then take that rank to get a running cost total.