cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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.

Jennifer

2 REPLIES 2
Community Support

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.

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.