topic Re: How to get highest top 5 percent of dataset based on a value column in Desktop

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

felixthecatx — Fri, 07 Jan 2022 15:30:30 GMT

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.

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

smpa01 — Fri, 07 Jan 2022 15:39:03 GMT

felixthecatx what is the desired output for this dataset?

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

felixthecatx — Fri, 07 Jan 2022 16:35:39 GMT

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.

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

smpa01 — Fri, 07 Jan 2022 16:45:28 GMT

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 ) ) )

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

parry2k — Fri, 07 Jan 2022 17:42:11 GMT

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.

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

felixthecatx — Fri, 07 Jan 2022 17:51:38 GMT

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

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

parry2k — Fri, 07 Jan 2022 18:01:21 GMT

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

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

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

felixthecatx — Fri, 07 Jan 2022 18:30:59 GMT

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

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

parry2k — Fri, 07 Jan 2022 19:33:28 GMT

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

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

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

felixthecatx — Fri, 07 Jan 2022 20:46:05 GMT

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?

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

felixthecatx — Mon, 10 Jan 2022 12:59:13 GMT

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

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

Anonymous — Fri, 21 Jan 2022 21:57:32 GMT

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?