dynamic list of customer based on percentiles

[sturlaws]

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,

Sturla

Link to sample file

[Anonymous]

@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

[Anonymous · Accepted solution]

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