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 18, 2020 in Power BI by Rajiv
• 8,910 points
1,795 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,890 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,890 points
2,369 views
0 votes
0 answers

How can I create dynamic parameters in Power BI using DAX?

How can I create dynamic parameters in ...READ MORE

Oct 14 in Power BI by anonymous
• 2,560 points
39 views
0 votes
1 answer

How can I create dynamic parameters in Power BI using DAX?

Similarly, in Power BI, utilizing DAX, one ...READ MORE

answered Oct 22 in Power BI by pooja
• 1,220 points
78 views
0 votes
0 answers
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,890 points
566 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,890 points
1,974 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,890 points
2,858 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 25, 2020 in Power BI by Gitika
• 65,890 points

edited Sep 25, 2020 by Gitika 5,151 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,890 points
1,265 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP