Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sturlaws
Resident Rockstar
Resident Rockstar

dynamic list of customer based on percentiles

Hi,

 

I need to generate a list of customers who have placed more orders than the 90% percentile. The list needs to respond to filters on time, product, segment and shop. 

This measure gives me the percentile:
Percentile 90% = VAR customerTable =
CALCULATETABLE ( VALUES ( 'Dim Customers'[Customer ID] ); 'Fact Orders' )
RETURN
PERCENTILEX.INC ( customerTable; [Number of orders];0,9)

But when I add this measure to a table together the customerID, the percentile is calculated within the context of each row in the table, so that the percentile value equals the number of orders. I have tried different variations changing the filter context by ALL, ALLSELECTED, etc, but I cannot get it to work properly.
 
Anyone with suggestions on how to solve this, short of calculated columns?
 
regards,
 
percentile.PNG
 


 
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @sturlaws ,

 

Current power bi not support to create dynamic calculated column/table based on filter/slicer.

It is possible to create dynamic measure show dynamic result based on current row contents and filter effects, but if you use it in calculate column.
Row contents will fix to whole table and calculation result not dynamic change by filter/slicer.

 

Regards,

Xaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
vmakhija
Post Prodigy
Post Prodigy

@sturlaws 

I didn't try the M script on this page myself, but see if it helps -

https://social.technet.microsoft.com/Forums/en-US/a57bfbea-52d1-4231-b2de-fa993d9bb4c9/can-the-quotp...

 

Regards

v-shex-msft
Community Support
Community Support

Hi @sturlaws ,

 

Current power bi not support to create dynamic calculated column/table based on filter/slicer.

It is possible to create dynamic measure show dynamic result based on current row contents and filter effects, but if you use it in calculate column.
Row contents will fix to whole table and calculation result not dynamic change by filter/slicer.

 

Regards,

Xaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.