cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Top 10 by different measures on a slicer

Hello, 

 

I'm relatively new to PBI so this issue might very well be something that has already been covered elsewhere (and I just haven't been able to find it in my searches over the past few days; I have done a good bit of looking but perhaps I have missed something). 

 

I have seen many articles/videos/etc describing in great detail how to create a slicer that can dynamically switch between different Top Ns such as Top 5, 10, 15, 20, etc. However, an end user of mine would like something a little different. In short, they don't need a selection of top n's, they are fine with having only Top 10.

 

For context, I have created a rather simple matrix for them that has a dimension of Merchant Type and 3 simple measures: $ Amount, # of Transactions, and $/Transactions. What they would like is this: 1) to have a slicer that has the 3 measures as values to select, 2) for the matrix to adjust based on this selection and to return the respective Top 10 by the selection while keeping the other 2 measures for those particular 10 merchant types. So essentially they would like to be able to easily toggle between the 'By' portion of a Top 10 by filter without having to use the filter pane and manually change the by selection. 

 

Here is an example: the user selects '$ Amount' on the slicer and the matrix adjusts to show the Top 10 merchant types by $ Amount with those 10 merchants' # of Transactions along with their $/Transaction. If they select '$/Transaction' on the slicer, they will see the Top 10 merchant types by $/Transaction with those 10 merchants' $ Amount and # of Transactions. 

 

If this is something that is possible, any tips/resources/assistance would be greatly appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Top 10 by different measures on a slicer

I believe this approach will work for you.

 

Make a slicer from a disconnected table with the list of measures, and a SWITCH(TRUE(), ... type measure following the approach explained here - https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/

You then can add a TopN filter to the category column in your visual in the Filter panel, and use the new measure.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: Top 10 by different measures on a slicer

Measures cannot be used on slicers. Measures always return a single (scalar) value based on the filter context - the row and column of the matrix, other slicers, filters in the filter pane, etc.

 

So while you think a measure is returning 100 values and you want to set it to show the top 10 in a slicer, it is really only returning 1 value 100 different ways for each time the measure shows up in your matrix.

 

You can use a filter on the FILTER pane for reducing the data visible, but it is simple things, like less than. It cannot do TOP 10 because what does top 10 mean? Top 10 each year, each region, each month? So it only works in absolute dollar amounts.

 

You might look at this article to see if it helps achieve your goal.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Frequent Visitor

Re: Top 10 by different measures on a slicer

I see. So, to be clear, there is no way that you are aware of to dynamically change the "By" portion of a Top 10 Merchant Types by XYZ filter on a report? 

 

(Thank you for your response, and provided link btw)

Highlighted
Community Champion
Community Champion

Re: Top 10 by different measures on a slicer

I believe this approach will work for you.

 

Make a slicer from a disconnected table with the list of measures, and a SWITCH(TRUE(), ... type measure following the approach explained here - https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/

You then can add a TopN filter to the category column in your visual in the Filter panel, and use the new measure.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

Highlighted
Frequent Visitor

Re: Top 10 by different measures on a slicer

Thank you very much @mahoneypat ! I actually came across this same article earlier today and didn't think to use it the way you described. 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors