PowerPivot DAX - Dynamic Ranking Per Group Min Per Group

Question

I am searching for a method to utilize within Microsoft PowerPivot 2010 that will allow me to perform dynamic ranking that will automatically update the associated rank value based on filters and slicer values that are applied.

I would greatly appreciate if someone can outline how this could be accomplished.

Answer 1

Firstly create a basic measure [Amount] to sum the dollar amount column. I then used that within RANKX() to create the following:

[Rank] = RANKX(
         FILTER(
         ALLSELECTED(Table1),Table1[Claimant Number]=max(Table1[Claimant Number])
                   ),
         [Amount],
            ,1)

The key is the table that the [Amount] measure is iterated over - ALLSELECTED() just brings the stuff in the current filter context into play and the expression within the FILTER() restricts the table to the current claim number.

After that it was a simple task to return the [Amount] based on whether or not the [Rank] was 1:

[Opening Balance] = if([Rank]=1,[Amount],BLANK())