Show only Top 10 AND Bottom 10 (hide everything in middle)

Anything related to PowerPivot and DAX Formuale
bkrol
Posts: 12
Joined: Tue Apr 16, 2019 8:52 am

Show only Top 10 AND Bottom 10 (hide everything in middle)

Postby bkrol » Fri Jun 14, 2019 12:54 am

I have a pivot table that I have sorted by customer by variance from last year by smallest to largest.

However, I have 500 customers. I really only want to see the 10 customers that had the largest negative variance and the 10 customers with the largest positive variance.

I can use the values filter -> top 10 feature, but then I would have to do 2 pivot tables (one for the top 10 negative variances and one for the top 10 positive variances).

Is there a way I can suppress or hide all the customers in the middle?
Attachments
Top 10 and Bottom 10.xlsx
(18.69 KiB) Downloaded 38 times

MattAllington
Posts: 1103
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Show only Top 10 AND Bottom 10 (hide everything in middle)

Postby MattAllington » Sun Jun 16, 2019 9:09 am

I guess it is possible.
Write 2x rankx functions to determine the top and bottom items, then use an IF statement to show or hide based on he results.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

sophiabrar
Posts: 2
Joined: Mon Jan 13, 2020 12:01 am

Re: Show only Top 10 AND Bottom 10 (hide everything in middle)

Postby sophiabrar » Mon Jan 13, 2020 12:29 am

Hi Matt-
Both the links are broken to access the adventure works database-the one in the book and the one you shared in your posts below.
https://exceleratorbi.com.au/power-bi-online-training/


Please let me know from where to get the access database?


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 13 guests