Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

  • 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 () )

    • Anonymous's avatar
      Anonymous
      Not 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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User

        Hi,

        Please try the below.

         

        Measure_Rank =
        VAR newtable =
        SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Customer ID] )
        RETURN
        RANKX ( newtable, [Frequency],, ASC )