Forum Discussion
Percentile based on slicers selection
Hi Experts,
I have a question struggling me for a long time, I thought that it would be easy but it's harder than I think.
I searched so hard but cannot find a similar post in this community either.
Please find my attached PBIX RFM Demo.pbix
In my dashboard, I would like to calculate the percentile of Frequency by the Customer ID based slicers selection.
Firstly, I would like to explain my method.
I will create a Rank, which I expect to be from 1 to 305 based on my current slicer selection, but it's now from 16353 to 16657.
Measure_Rank = CALCULATE( RANKX(ALL('Table'[Customer ID]),[Frequency], ,ASC) )2nd measure which I expect to be 305, but it's now 16657
Measure_Count = COUNTX (ALL('Table'[Customer ID]), SUMX('Table', [Frequency] ))3rd measure is my expected result, divide Rank by Count, so I can get the percentile.
Measure_Percentile = DIVIDE([Measure_Rank],[Measure_Count])
Actualy, in Excel there is a function PERCENTRANK.INC, however it's absent in DAX.
and PERCENTRANK.INC is what I expect.
My expected result RFM Result.xlsx
I hope that anyone help me, thanks so much! (You may not follow my method)
Hi,
I am not sure if I understood your question correctly, but please try to change the count measure like below.
Measure_Count =
CALCULATE ( COUNTROWS ( VALUES ( 'Table'[Customer ID] ) ), ALLSELECTED () )Hi,
Please try the below.
Measure_Rank =
VAR newtable =
SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Customer ID] )
RETURN
RANKX ( newtable, [Frequency],, ASC )
4 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but please try to change the count measure like below.
Measure_Count =
CALCULATE ( COUNTROWS ( VALUES ( 'Table'[Customer ID] ) ), ALLSELECTED () )- AnonymousNot applicable
Jihwan_Kim Thank you very much! it works.
I never knew the ALLSELECTED parameter could be blank.
It would be fine if you could solve Measure_Rank as well- Jihwan_Kim
Super User
Hi,
Please try the below.
Measure_Rank =
VAR newtable =
SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Customer ID] )
RETURN
RANKX ( newtable, [Frequency],, ASC )