Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rlaughli
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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
edhans
Super User
Super User

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

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)

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.