Sorting by ID within a column and getting top % of posts possible?

[natabird3]

Hello,

I am trying to do the following: I have a distinct ID column of customers; each customer has ceratin purchase rate in %;

What i am trying to do is sort the purchase rate by largest to smallest for each distinct ID, then once that is done i need to get top 2%, 5%, 10%, 20%, 25%, and 50% of all purchase rates from the distinct ID. The problem is if ID 1 has 10 purchase rates when we do top 2% of 10, top 5% of 10, top 10% of 10 etc. it will be different for the next ID2 for example which may have instead of 10, 100 purchase rates. Then the number for each % will be different (top 2% from 100, top 5% from 100 etc.). So in the end the result i need is those 6 numbers for each of the ID's as this will basically be my benchmark for the period. Is this sort of sorting possible in some way in PowerBi? In excel its easy but here i am not sure how to go about it, if anybody can help that would be greatly appreciated. Thanks.

Regards,

Jordan

[v-yuta-msft]

@natabird3 ,

So you want to achieve the TopN values in each ID, right? In this senario, you can create an additional rank column to give the value rank in each ID. Then build another table using the distinct ID column and build a slicer based on that column, write a switch variable so that when selection has been selected in the column you will achieve a corresponding Percentage.

Finally, write a measure using that percentage to achieve the TopN value.

Community Support Team _ Jimmy Tao

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

[natabird3]

What is a switch variable sorry? And the idea of this new ID column to sort by column later on? Please explain, thanks for your help. 

Regards,

Jordan 

[natabird3]

Actually maybe what would be easier is:

I need to sort one of the columns by top 10%. At the moment what i am allowed to do is only sort by top N as a number. Is there a way to sort top 10% of a column. Meaning if i have different IDs and each one has some values linked to it, if i sort top 10% of all those vlaues linked to the ID the issue will be solved. If anybody can help that would be appreciated. Thanks in advance. 

[v-yuta-msft]

@natabird3 ,

Could you please share some sample data?

Regards,

Jimmy Tao

[natabird3]

					ID	Purchase
						66
Grade	Number of P	Threshold	Purchase		1	8.48%
A+	1 (out 66 P)	2%	8.23%		1	8.23%
A	3 (out of 66 P)	5%	6.88%		1	6.75%
B	7 (out of 66 P)	10%	5.60%		1	5.66%
C	13 (out of 66 P)	20%	4.67%		1	5.09%
D	17 (out of 66 P)	25%	4.15%		1	5.02%
E	33 (out of 66 P)	50%	2.73%		1	4.30%
					1	4.13%
					1	4.00%
					1	3.86%
					1	3.69%
					1	3.53%
					1	3.34%
					1	3.08%
					1	2.90%
					1	2.74%
					1	2.36%
					1	1.79%
					1	1.69%
					1	1.54%
					1	1.49%
					1	1.43%
					1	1.39%
					1	1.32%
					1	1.26%
					1	1.25%
					1	1.24%
					1	1.18%
					1	1.17%
					1	1.12%

So basically what i need is the bolded number value for each separate ID. Here is one ID only but the structure and idea is same for every other ID. Hope this helps explain waht i am trying to do. Thanks for helping. 

[v-yuta-msft · Accepted solution]

@natabird3 ,

I suppose the threshold is based on total purchase amount/rows in each ID. You can create two calculate columns to achieve this(The result is not same with your sample):

Rank = RANKX(FILTER('Table', 'Table'[ID] = EARLIER('Table'[ID])), 'Table'[Purchase], , DESC, Dense)

Percentage ID 1 = CALCULATE(MAX('Table'[Purchase]), FILTER('Table', 'Table'[Rank] = ROUNDUP(MAX('Table'[Rank]) * Rate[Threshold], 0)))

 

Community Support Team _ Jimmy Tao

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

[natabird3]

Yes result is different as i only provided some rows apologies. But this works, thanks for help.