The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
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
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.
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
User | Count |
---|---|
28 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |