How to get highest top 5 percent of dataset based on a value column

[felixthecatx]

ID	Value
1	4
2	88
3	23
4	12
5	45
6	120
7	13
8	58
9	78
10	3

For the example table above, how do I display only the top (highest) 5% of dataset based on the value column? Top N only allows for numbers. I'm looking for something that can grab highest 5% of data based on a column. 

[parry2k]

@felixthecatx I'm sorry I missed to add that measure

Sum Value = SUM ( YourTable[Value Column] )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@felixthecatx let's you have table visual, add Id and Top X measure in the table and that will do it, top records is just to calculate how many top Ids we want to see. I hope it helps, if need more info, please let me know.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[felixthecatx]

Ah okay I see. What is 'Sum Value' referring to in Top X? 

[parry2k]

@felixthecatx it is pretty straight forward, I have used what-if allows to select the % but you can fix it in the measure if you like it

and here are the measure, you can always do it in one measure if you want:

Top Records = 
VAR __totalRows = CALCULATE ( COUNTROWS ( VALUES ( 'Top Based'[ID] ) ), ALL ( ) )
VAR __topRate = [Top % Value] //enter % value here I'm using what-if parameter
VAR __topRows = __totalRows * __topRate
RETuRN __topRows

Top X = CALCULATE ( [Sum Value], KEEPFILTERS ( TOPN ( [Top Records], ALL ( 'Top Based' ), [Sum Value], DESC ) ) )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[felixthecatx]

@parry2k When I add this as a measure, how do I use it to get the table? 

[smpa01]

@felixthecatx  what is the desired output for this dataset?

[felixthecatx]

Hi @smpa01 ,

The sample dataset is actually not a great example, sorry. But say we have a dataset with 120 values. I need the top 5% (In this case 6 records) highest values in a seperate table. 

[smpa01 · Accepted solution]

@felixthecatx  thanks for defining TOPN%.

let's suppose on the given dataset you want TOP60% (as per your definition), you can do the following

Measure = 
VAR _sum =
    SUM ( 'Table'[Value] )
VAR _top =
    CALCULATE (
        COUNTROWS ( ADDCOLUMNS ( VALUES ( 'Table'[ID] ), "_sum", _sum ) ),
        REMOVEFILTERS ( 'Table'[ID] )
    ) * 0.6
RETURN
    CALCULATE (
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        KEEPFILTERS (
            TOPN (
                _top,
                ALLSELECTED ( 'Table'[ID] ),
                CALCULATE ( SUM ( 'Table'[Value] ) ), DESC
            )
        )
    )

[felixthecat_nyc]

@smpa01 I have a few questions concerning this method.

1. It works with grabbing values and summing them (in my case by ID). What if I wanted instead of a SUM to provide the max value per ID? When I replace SUM with MAX in the snippet you provided, it no longer grabs N% of the dataset, but shows entire dataset in table. 

2. If I don't change the script you provided, but then add other columns to the table, it no longer shows top N%. Is there a way to make this dynamic with other fields in my data set? 

[felixthecatx]

@smpa01 Thanks for this it worked on my dataset after some tweaking (I have repeating ID's with different values so I had to work around that). What I still don't understand is how does the script know to take the topN% and not the low or middle? 

[felixthecatx]

Ah I see it's the 'DESC', thanks.