Showing dynamic top 25%

[DMSJennifer]

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

[Anonymous]

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]

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