Good afternoon everybody,
I hope you are well.
I have a list with all the sites, the overdues and the period.
On tab "Power Pivot" I want to add a new measure to display top 5 sites (with highest number of overdues) per each quarter by using a slicer. In order to do so, I am using Power Pivot to change the measures of slicer. I want to use the function "Large" but it is not there when creating the new measure.
Anyone can help me, please?
Thank you
Last edited by viewotst; 02-07-2022 at 08:42 AM.
Not sure why you would need a measure to do this.
On the Power Pivot sheet:
1. From the filter/sort button in cell A3 select Values filter > Top 10 > in the dialog box change the 10 to 5
2. From the filter/sort button in cell A3 select More sort options > Descending (Z to A) by: Sum of overdues
By the way the same could be done on Sheet2 with the regular pivot table.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hello JeteMc,
Thank you for your help. Sorry, I did not explain it properly.
I am attaching a new file and I think this one is much more clear.
I have use the data model option. In the data model, I have used a DAX formula to show the top5 values. However, I am struggling to do the second part. I would like to create another DAX formula to show the bottom 5 values and then once I have created both of them, create a pivot table with two slicers. The first one for the quarters (Q1 and Q2) and the second one for the 5 highest values and the 5 lowest values (Top5 and bottom5). Thus, if I want to show the bottom5 for the second quarters I have to click on Q2 on the periods slicer and bottom5 in the values slicers.
I hope you can help me.
Viewotst
Thanks
Modify the formula in the TOP5 column of the data model to read:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Last edited by JeteMc; 01-27-2022 at 04:52 PM. Reason: Added file
Many thanks JeteMc,
The only question that I have. If you see the results after using the slicers, you only see three top5 sites. If I wish to see 5, should I replace the 5 (in the DAX formula) with a 3 instead? Ie. We have 8 sites - 3 = 5.
Thanks
Viewotst
Sorry for asking in a separate reply, but how did you do so the DAX formula did not mix both periods (Q1 and Q2) so when you click on Q1 in the slicer it only shows the Q1 values. Is it because you use the formula in just one column instead of two?
Thanks again
Not sure that I understand the question in post #6.
As to my understanding of the request in post #5, it seems that you would like the top five per quarter to be displayed.
Replacing the 5 with 3 will only display the top 3 so I feel sure that isn't what you want.
That said, I imagine that using DAX to show the top 5 per quarter goes beyond my knowledge of DAX formulas. I will ask some of the other contributors to take a look.
Bear in mind that this is the beginning of the weekend for many contributors, so it may be a few days before someone responds.
At the risk of repeating an earlier statement let me point out that a regular pivot table will do exactly what you want, just apply a value filter as modeled in the file attached to post #2.
I hope that you have a blessed day.
Hi JeteMc,
Sorry if I was not clear. I was wondering how to do the top5 with DAX formulas as it is a file that people with not much knowledge in Excel will potentially use.
Thanks
Viewotst
Hi,
Ranking via a Calculated Column is not flexible in that Calculated Columns do not respond to filters. A Measure is better, which can then be used to filter the Pivot Table.
See the attached. I assumed that, if both periods are selected, the filtering should be based on that Site's total overdues across both those periods.
Regards
Last edited by XOR LX; 02-04-2022 at 11:52 AM.
Amazing, thank you a lot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks