Running Total Based on Rank with Filters

[cwermager]

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.

Area	JobID	Cost	Benefit	Efficiency	Rank	Cumulative Cost
A	300	1000	15000	15.0	1	1000
A	304	1500	8000	5.3	4	5200
A	308	1200	12000	10.0	2	2200
B	303	3000	-4000	-1.3	9	15700
B	301	2500	5000	2.0	8	12700
C	305	1500	14000	9.3	3	3700
C	306	1200	5000	4.2	5	6400
C	307	2000	6000	3.0	6	8400
C	302	1800	4000	2.2	7	10200

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:

Area	JobID	Cost	Benefit	Efficiency	Rank	Cumulative Cost
C	305	1500	14000	9.3	1	1500
C	306	1200	5000	4.2	2	2700
C	307	2000	6000	3.0	3	4700
C	302	1800	4000	2.2	4	6500

Any help would be greatly appreciated.

Thanks.

Chad

[FrankAT]

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]

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. 

[MattAllington]

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.