topic Re: Displaying the top N% of a category in DAX Commands and Tips

Displaying the top N% of a category

Anonymous — Thu, 06 Jan 2022 00:06:22 GMT

Hi,

I'm trying to write a measure where i'd be able to display the top N% of a category, possible this would dynamic so would change upon changing of the slicer. Is this possible?

Any help would be very much appreciated.

Re: Displaying the top N% of a category

VahidDM — Thu, 06 Jan 2022 00:37:50 GMT

Hi Anonymous

Can you provide a sample of your data and expected result in a text format?

BTW, check these links:

https://community.powerbi.com/t5/Desktop/Display-Top-N-by-Category-and-Sub-Category/m-p/2121269

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Show-Top-N-Rank-Category/m-p/2136606

https://community.powerbi.com/t5/Desktop/Top-N-Categories-within-the-Nth-Category/m-p/2175433

https://community.powerbi.com/t5/Desktop/Top-N-states-within-TOP-N-category/m-p/1828635

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Displaying the top N% of a category

Anonymous — Thu, 06 Jan 2022 01:12:28 GMT

Hi VahidDM,

Many thanks for your help. please see an image of some of the data. I'd be looking to see the top 20% of category of units sold changing each month.

Re: Displaying the top N% of a category

VahidDM — Thu, 06 Jan 2022 01:17:58 GMT

Hi Anonymous

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Displaying the top N% of a category

Anonymous — Mon, 10 Jan 2022 06:48:05 GMT

Hi Anonymous ,

The idea is to first calculate the sales ranking, and then take 20% of the ranking to display.

Sample data

Here's my solution.

1.Create a rank measure.

RANK = RANKX ( FILTER ( ALLSELECTED ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) ), CALCULATE ( SUM ( 'Table'[Sales] ) ), , DESC, DENSE )

You can get a sort grouped by year and month.

2.Create a flag measure.

FLAG = VAR _20 = ROUNDDOWN ( MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) ), [RANK] ) * 0.2, 0 ) RETURN IF ( [RANK] <= _20, 1 )

Put that measure into filters, set show items when the value is 1.

You can filter year month in the slicer.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.