Forum Discussion

DMSJennifer's avatar
DMSJennifer
Icon for Helper I rankHelper I
5 years ago

Showing dynamic top 25%

Hello,

I'm trying to find the top 25% customers based on peformance. I've figured out how to rank those customers based on that peformance, but if I want to count the amount of customers and multiply that by 0,25 to get a dynamic top selection. It doesn't work like I expect. Can you help me?

 

Top 25% Customer =
Var CustomerRank = RANKX(CROSSJOIN(ALLSELECTED(T_DRN_Jaar[KPGR_KGMMTot]),ALLSELECTED(InvoergegevensTable[IGKP_ID])), CALCULATE(SUM(InvoergegevensTable[NettoCashFlow])),,DESC)
VAR Rank_To_Find = CALCULATE(DISTINCTCOUNT(InvoergegevensTable[IGKP_ID]),InvoergegevensTable)/4
RETURN
CALCULATE(AVERAGE(InvoergegevensTable[NettoCashFlow]), FILTER(InvoergegevensTable, CustomerRank<= Rank_To_Find))

 

Rank_To_Find=CALCULATE(DISTINCTCOUNT(InvoergegevensTable[IGKP_ID]),InvoergegevensTable)/4

This is the formula I use to count the amount of customers. To filter the top 25% based on the ranking, I use the following formula:

FILTER(InvoergegevensTable, CustomerRank<= Rank_To_Find)
If I put a number in stead of "Rank_To_Find", its working like I want it to work, but if I use "Rank_To_Find". It doesn't work.
It seems like the Rank_To_Find doesn't provide some kind of number to compare the CustomerRank.  

Thanks in advance,

Jennifer

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi DMSJennifer 

    Can you provide some data for InvoergegevensTable and T_DRN_Jaar table ? From the formula you provided, I don't see any problems at present. It would be best if I can refer to the corresponding data

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • DMSJennifer's avatar
      DMSJennifer
      Icon for Helper I rankHelper I
      5 years ago

      Hello Ailsa,

       

      This is some sample data for T_DRN_Jaar table

       

      And this is some sample data for InvoergegevensTable:

       

      I've covered the sensitive data. I hope this can be usefull. 

       

      Kind Regards,

      Jennifer