Top N and All

[Anonymous]

Hi, 

I am still fumbling in the DAX world.

I have a SalesData table which has Customer Names in one column and Sales in another column. I have a visual to show the Top N Customers by Sales.  I have a slicer to slice the visual into Top 5, Top 10, Top 50. When user clicks one of the items, the visual will show the Top 5, Top 10, Top 50 Customer Names and their Sales. Here is how I did this:

I created a seperate Slicer table, with one column as slicer item names (TopN Name) which feeds into the slicer. The other column of the Slicer table is the slicer value (TopN Value: 5, 10, 50).  To "harvest" the slicer value, I have the following measure set up:

TopN Value Selected = if(HASONEVALUE(Slicer_Table[TopN Name]),VALUES(Slicer_Table[TopN Value]),BLANK())

Then the above measure is fed into the following 

Top N Customer by Sales =

CALCULATE([Sales],filter(values(SalesData[Customer Name]),[Rank Customer by Sales]<=[TopN Value Selected]))

The measure [Rank Customer by Sales] is defined as follows :

Rank Customer by Sales = RANKX(all(SalesData[Customer Name]),[Sales],,DESC,Dense)

[Sales] is a measure that sums up the column of Sales in the SalesData table: [Sales]=sum(SalesData[sales])

However, I want to add a fourth item to the slicer to show All, which is all the customers (not just the top ones). How do I incorporate this "All" into the slicer? (in other words, the slicer should have four items: Top 5, Top 10, Top 50 and All, when user click All, every customer shows up)?

Thanks for enlightment!

PBISean

[v-lili6-msft · Accepted solution]

HI, @Anonymous

You could try set your Slicer table like this "ALL" Value is 99999999

Then refer to this post

https://community.powerbi.com/t5/Desktop/Show-Top-N-and-sum-up-the-Others-as-a-value/m-p/313063#M138849

https://community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672

Just adjust the formula

Best Regards,

Lin