Home Forums Power Pivot Filtering Rankings

Tagged:

This topic contains 2 replies, has 2 voices, and was last updated by  nullspace 3 years, 3 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
• Author
Posts
• #3854

nullspace
Participant
• Started: 3
• Replies: 1
• Total: 4

I have a table of IDs and salaries, for which I want to see the top ID’s and their respective ranks for only the salaries over a certain amount. I also want to be able to enter in this threshold and have the Pivot table reflect these changes.

1) What Measure can I use to show this?

<span style=”font-size: 16px; line-height: 1.5;”>2) Should I consider using a calculated column or my moving in the right direction?</span>

Thanks

#3875

tomallan
Keymaster
• Started: 7
• Replies: 2556
• Total: 2563

If you are thinking of values that vary in response to slicers, you are most likely not going to be satisfied only with a calculated column.

You may actually have several formulas along the way which will include a calculated column and measure(s). Functions that you should look into are CALCULATE(), RANKX(), FILTER() and ALL().

If you have not already done so, I recommend getting an excellent book on Power Pivot, such as “Power Pivot and Power BI” by Rob Collie and Avi Singh.

It also would not hurt for you to check out Rob’s Comprehensive Power Pivot Course (self-paced, online video training).

#3881

nullspace
Participant
• Started: 3
• Replies: 1
• Total: 4

I eventually figured it out after a lot of trial and error.  I used a combination of 2 Measures: a ranking formula and  calculate(rankformula,filter(all(table[column])),rankformula=nth).

I was originally calling an entire table inside of all() instead of just a column and that did the trick. Rob and Avi’s book is amazing and would recommend it to anybody learning powerpivot.