cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
natabird3 Regular Visitor
Regular Visitor

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

Capture.PNG 

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.

 

7 REPLIES 7
Community Support Team
Community Support Team

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

@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 Regular Visitor
Regular Visitor

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

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 Regular Visitor
Regular Visitor

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

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. 

Community Support Team
Community Support Team

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

@natabird3 ,

 

Could you please share some sample data?

 

Regards,

Jimmy Tao

natabird3 Regular Visitor
Regular Visitor

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

     IDPurchase
      66
GradeNumber of PThresholdPurchase 18.48%
A+1 (out 66 P)2%8.23% 18.23%
A3 (out of 66 P)5%6.88% 16.75%
B7 (out of 66 P)10%5.60% 15.66%
C13 (out of 66 P)20%4.67% 15.09%
D17 (out of 66 P)25%4.15% 15.02%
E33 (out of 66 P)50%2.73% 14.30%
     14.13%
     14.00%
     13.86%
     13.69%
     13.53%
     13.34%
     13.08%
     12.90%
     12.74%
     12.36%
     11.79%
     11.69%
     11.54%
     11.49%
     11.43%
     11.39%
     11.32%
     11.26%
     11.25%
     11.24%
     11.18%
     11.17%
     11.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. 

Community Support Team
Community Support Team

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

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

Capture.PNG 

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 Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 322 members 3,416 guests
Please welcome our newest community members: