Running total based on RANKX measure

[smpa01]

I have inherited a terrible data model and attached is the minimally reproducible example to address the current issue that I am facing.

There are two tables that have M-M relationship and none of them have any date column. In the process of analysis, there is a measure called `remaining` gets created and I am doing a Rank of  `remaining` group by `alt1`. Once the ranking is done, I want to achieve a running total of  `remaining` group by `alt1`. 

I am getting this

I am desiring this (without having to make any changes to the data model whatsoever)

| alt1 | alt2  | cost        | sales       | remaining   | rank | rnt         |
|------|-------|-------------|-------------|-------------|------|-------------|
| aus1 | amr21 | $10,805.00  | $10,805.00  | $0.00       | 1    | $0.00       |
| aus1 | amr23 | $260.00     | $260.00     | $0.00       | 1    | $0.00       |
| aus1 | amr24 | $10,776.00  | $4,184.24   | $6,591.76   | 3    | $6,591.76   |
| aus1 | amr26 | $52,277.00  | $4,000.00   | $48,277.00  | 6    | $54,868.76  |
| aus2 | amr22 | $22,916.00  | $9,621.95   | $13,294.05  | 4    | $13,294.05  |
| aus2 | amr25 | $2,859.00   | $2,859.00   | $0.00       | 1    | $0.00       |
| aus2 | amr27 | $2,988.00   | $2,988.00   | $0.00       | 1    | $0.00       |
| aus3 | amr29 | $4,019.00   | $3,423.36   | $595.64     | 2    | $595.84     |
| aus3 | amr32 | $512.00     | $511.80     | $0.20       | 1    | $0.20       |

@AlexisOlson I followed this but could not make it to work.

@CNENFRNL 

Thank you in advance.

[AlexisOlson · Accepted solution]

You're iterating over the wrong granularity. You need to iterate over the granularity that your ranking corresponds to.

Try this:

rnt = 
var _currentRank = [rank]
VAR _grain = CALCULATETABLE ( VALUES ( sales[alt2] ), ALLSELECTED ( sales[alt2] ) )
return
    SUMX (  FILTER ( _grain, [rank] <= _currentRank ), [remaining] ) 

You can even simplify this to 

rnt = 
var _currentRank = [rank]
return
    SUMX (  FILTER ( ALLSELECTED ( sales[alt2] ), [rank] <= _currentRank ), [remaining] ) 

but I don't recommend using ALLSELECTED inside iterators and this bakes in some assumptions about alt2 that might not hold in general.

[smpa01]

@AlexisOlson  Thank you Sir !!!

[v-henryk-mstf]

Hi @smpa01 ,

What is the rankx measure created based on and can you provide the relevant formula for creating it? Looking forward to your reply.

Best Regards,
Henry

[smpa01]

@v-henryk-mstf  thanks fort he response. Please take a look at the attached pbix. It contains all the corresponding calculations.