Need help with Decile Rank

Question

Hi Everyone,&nbsp;I have a 'client statistics' table which is a calculated table and has attributes like Month end, client id, sales.I am trying to add a new column to this table 'Sales Decile Rank' which would group the client ids every month into 10 groups based on the sales amount.Below is the dax that I have developed-&nbsp;_client statistics =&nbsp;var _deciles=&nbsp; &nbsp; ADDCOLUMNS(_segments,"Decile Rank",&nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.1),10,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.2),9,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.3),8,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.4),7,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.5),6,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.6),5,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.7),4,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.8),3,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Client Sales]&lt;= PERCENTILEX.INC(_segments,[Client Sales],0.9),2,1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; )return _deciles &nbsp;Where _segments is another variable . When I execute this , I only get Deciles 1, 2 , 3 and 10. Ideally I should get values from 1 to 10.What am I missing here ? Any guidance is appreciated.&nbsp;Thank you

nabandla · Answer

From the provided code, it seems that the variable `_segments` is not defined, which might be causing the issue with the calculation of decile ranks. Here's a modified version of the code that should work correctly:

```dax
_client statistics =
VAR _segments =
SUMMARIZE('client statistics', 'client statistics'[Month end], 'client statistics'[client id], "Client Sales", SUM('client statistics'[sales]))
VAR _deciles =
ADDCOLUMNS(
_segments,
"Decile Rank",
SWITCH(
TRUE(),
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.1), 1,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.2), 2,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.3), 3,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.4), 4,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.5), 5,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.6), 6,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.7), 7,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.8), 8,
[Client Sales] <= PERCENTILEX.INC(_segments, [Client Sales], 0.9), 9,
10
)
)
RETURN _deciles
```

In this modified code:

- The `_segments` variable uses the `SUMMARIZE` function to create a summarized table based on the 'client statistics' table, grouping it by 'Month end' and 'client id' and calculating the total 'Client Sales' using the `SUM` function.
- The `SWITCH` function is used instead of nested `IF` statements to assign the appropriate decile rank based on the sales value.
- The `SWITCH` function handles the scenarios where the sales value falls into each specific decile range, and if none of the conditions are met, it assigns a rank of 10.

Please give this updated code a try, and it should provide the desired decile ranks for your client statistics table. Let me know if you have any further questions!

_regina · Answer

Thank you for your reply. It still does not give me all the deciles. It only gives me 1,8,9,10&nbsp;

Forum Discussion

Need help with Decile Rank

2 Replies

Related content

Deciles and Ranking

Calculating Deciles in Power BI

Customer Churn Decile Formula

How to create a dynamic decile ranking

QoQ Sales Decile Within Category