Unable to create calculated column for decile calculation

[Ankit_Monga]

var sgmt = AHT_summary[Business Segment]
var proc = AHT_summary[Process]
var subproc = AHT_summary[Sub Process]
var month = AHT_summary[Month Year]
Var decile_tab=CALCULATETABLE(SELECTCOLUMNS(AHT_summary,"Month",AHT_summary[Month Year],
"Business Segment",AHT_summary[Business Segment],"Process",AHT_summary[Process],"Sub Process",AHT_summary[Sub Process],"AHT",value(AHT_summary[AHT])),AHT_summary[Month Year] = month,
AHT_summary[Business Segment] = sgmt, AHT_summary[Process] = proc,
AHT_summary[Sub Process] = subproc)
var main=CONCATENATEX(decile_tab,SWITCH(TRUE(), value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.2), "Decile 1",

value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.2), "Decile 2",
value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.3), "Decile 3",
value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.4), "Decile 4",
value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.5), "Decile 5",
value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.6), "Decile 6",
value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.7), "Decile 7",
value(AHT_summary[AHT])< PERCENTILE.EXC([AHT], 0.8), "Decile 8",
value(AHT_summary[AHT]) < PERCENTILE.EXC([AHT], 0.9), "Decile 9",
"Decile 10"))
Return
main

[v-yueyunzh-msft]

Hi , @Ankit_Monga 

For your dax, Is there an error message when creating?

Depending on the query, you can check out the following links, which we hope will help you:

CONCATENATEX function (DAX) - DAX | Microsoft Learn

PERCENTILE.EXC function (DAX) - DAX | Microsoft Learn

Best Regards,

Aniya Zhang

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

[Ankit_Monga]

Thanks for your reply. I don't get any error meassge for it but Calculatable doest not give a filtered table to the percentile formula to calculate the decile, I am getting decile but that is calculating on the whole number not on the filtered table.

[v-yueyunzh-msft]

Hi , @Ankit_Monga 

I think it may have error in your decile_tab. You can try to use FILTER() function to filter your table then use the SELECTCOLUMNS() to select columns you need.

For another, you can use the sprcial number to your parameter above this, then clcik "New Table" to test if the generate table is right or not.

Best Regards,

Aniya Zhang

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