Top N Customer Names as a slicer in power BI

2
votes

i am new to power BI, i have a requirement to create a report which will show the data for top N customer based on sales amount. once i have identify the top N customer i need other charts / table / graphs to show the data of these N customer. so eventually i need a slicer that will show me the top N customer based on sales.

i have gone through various post, there they dynamic way of finding the top N (disconnected table) and then customer name as a measure is possible. when i combine this top customer measure with other data, the row that match with the top N customer shows the topN measure and rest is showing blank.

this is good but this does not solve my requirement. i need to find the top N customer dynamically and then report on those only and have a slicer for the same so that i can see the data for a particular customer

some google link suggested that this is not possible at all in power bi yet. "https://community.powerbi.com/t5/Desktop/top-N-slicer/m-p/171256"

please help in fixing the problem

2
ssaspowerbidaxpowerbi-embedded

2 Answers

2
votes

Unfortunately, visual level filters for slicers don't work. One possible workaround would be to use a table as a slicer by using the cross-filtering.

Put the customers in a table and use Top N filtering on the customer field using your sales measure as the value to rank by.

Here's an example of top 3 ranking without any customer selected: Ranked

If you click on one of the customers in the left table, then your table on the right will be cross-filtered to only show data for that one customer:

Cross-Filtered

1
votes

This is possible to some level with the April 2018 update of Power BI.

Refer this April2018 -Update on Numerical Slicer Page for additional Information on this.

For a Demo here, I am starting with this data:-

Customer    Sales
A            100
B            200
C            300
D            400
E            500

Then I create a conditional column using the formula of RANKX as below,

Top N = RANKX(Table1,Table1[Sales],,DESC,Dense)

So now put that Top N in the Slicer and it will allow you to select the from and to range for customers.

TOP N Customers