Displaying the top N% of a category

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous · Accepted solution]

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.

[VahidDM]

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/

[VahidDM]

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/