PowerPivot DAX - Dynamic Ranking Per Group Min Per Group

0 votes

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.

Nov 17, 2020 in Power BI by Rajiv
• 8,880 points
162 views

1 answer to this question.

0 votes

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())
answered Nov 18, 2020 by Gitika
• 65,870 points

Related Questions In Power BI

0 votes
1 answer

How to use Dynamic DAX Number Format in power BI?

You can give them the same display ...READ MORE

answered Dec 8, 2020 in Power BI by Gitika
• 65,870 points
252 views
0 votes
1 answer

Max count of workspaces per one PRO user

Right now, it is 250, will be ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 18,550 points
125 views
0 votes
1 answer

Group by columns on Power BI

On power BI desktop, do the following Add ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 52,310 points
196 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 52,310 points
348 views
0 votes
1 answer

Excel PowerPivot DAX Calculated Field

You should create a relationship between each ...READ MORE

answered Oct 5, 2020 in Power BI by Gitika
• 65,870 points
61 views
0 votes
1 answer

What is the difference between PowerPivot, Power Query and Power BI?

Power Query Power Query is a self-service ETL ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,870 points
100 views
0 votes
1 answer

DAX EARLIER() function in Power Query

Solutions in the code below. Notice that ...READ MORE

answered Oct 1, 2020 in Power BI by Gitika
• 65,870 points
287 views
0 votes
1 answer

DAX Calculation the Max for a Group.

You can try this out:  Desired Output=CALCULATE(MAX([Check]), ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,870 points

edited Sep 25, 2020 by Gitika 966 views
0 votes
1 answer

DAX / PowerPivot query functions to spread aggregated values over time period

The solution below assumes a table called ...READ MORE

answered Oct 5, 2020 in Power BI by Gitika
• 65,870 points
124 views