Forum Discussion

aukev's avatar
aukev
Helper III
7 years ago
Solved

Getting equal quartiles from a ranking

I have a list of around 80.000 customers. I`m trying to rank them based on how often they have purchased. For this I've created a calculated column called Frequency. This is basically just a count of all orders. 

 

What I want to achieve is rank the customers based on this and then put them in equal quartiles based on their ranking. So Q1 has 20.000 customers, Q2 has 20.000 customers etc..

 

To achieve this I`m following this example: https://community.powerbi.com/t5/Desktop/quartile-ranking/m-p/560850?collapse_discussion=true&filter=location&location=forum-board:power-bi-designer&q=quartiles&search_type=thread

 

I created a measure called Frequency Rank. And that works fine. But when I use the  code to create the quartiles the visual I`m using the Frequency Quartile measure in just keeps loading forever

 

 

Frequency Rank = 
RANKX ( ALL ( magentoCustomers[customerID] ), CALCULATE ( SUM ( magentoCustomers[Frequency] ) ) )
Frequency Quartile = 
VAR _table =
    SUMMARIZE ( ALL ( magentoCustomers ), magentoCustomers[customerID], "_Rank", [Frequency Rank] )
VAR _table1 =
    ADDCOLUMNS ( _table, "_Rank1", RANKX ( _table, [_Rank] ) )
VAR Percentile25 =
    PERCENTILEX.EXC ( _table1, [_Rank1], 0.25 )
VAR Percentile50 =
    PERCENTILEX.EXC ( _table1, [_Rank1], 0.5 )
VAR Percentile75 =
    PERCENTILEX.EXC ( _table1, [_Rank1], 0.75 )
RETURN
    IF (
        [Frequency Rank] < Percentile25,
        "Q1",
        IF ( [Frequency Rank] < Percentile50, "Q2", IF ( [Frequency Rank] < Percentile75, "Q3", "Q4" ) )
    )

Example data set:

 

  • Hi aukev ,

    Based on my test, you could use below formula to rank your data:

    Frequency Rank = COUNTROWS(FILTER(ALL('magentoCustomers'),ISONORAFTER('magentoCustomers'[Frequency],SELECTEDVALUE(magentoCustomers[Frequency]),DESC,'magentoCustomers'[customerID],SELECTEDVALUE(magentoCustomers[customerID]),DESC)))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

3 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee

    Hi aukev ,

    From your measure, I got below result:

    What is your desired result? Could you please post it?

     

    Regards,

    Daniel He

    • aukev's avatar
      aukev
      Helper III

      v-danhe-msft 

      The expected result would be this:

      customerIDFrequencyRankQuartile
      8277072Q4
      8277134Q3
      8277309Q2
      82774111Q4
      8277563Q4
      8277635Q3
      82777010Q1
      8277817Q2
      8277918Q2
      82780011Q1
      82781012Q1
      8278226Q3
      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee

        Hi aukev ,

        Based on my test, you could use below formula to rank your data:

        Frequency Rank = COUNTROWS(FILTER(ALL('magentoCustomers'),ISONORAFTER('magentoCustomers'[Frequency],SELECTEDVALUE(magentoCustomers[Frequency]),DESC,'magentoCustomers'[customerID],SELECTEDVALUE(magentoCustomers[customerID]),DESC)))

        Result:

        You could also download the pbix file to have a view.

         

        Regards,

        Daniel He