Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi.
I have created sample table as below.
The status of the column will be displayed whether the KPI is Not Stated, Below Threshold, Threshold, Min or Meet Target based on the achievement of each KPI above.
Below is the formula I am currently using however I am unable to cater if the trend type is Descending. I just realized that I have such data and that is why I have added another column name Trend as my formula indicator.
Formula
Achievement Status =
SWITCH(
TRUE(),
tbl_kpi_submission[achievement] => "0" && tbl_kpi_submission[achievement] < tbl_kpi_submission[Threshold],"Below Threshold",
tbl_kpi_submission[achievement] >= tbl_kpi_submission[Threshold] && tbl_kpi_submission[achievement] < tbl_kpi_submission[Min],"Threshold",
tbl_kpi_submission[achievement] >= tbl_kpi_submission[Min] && tbl_kpi_submission[achievement] < tbl_kpi_submission[Target],"Min",
tbl_kpi_submission[achievement] >= tbl_kpi_submission[Target],"Target","Not Started")
Appreciating someone can help me with this. I'm new and the above formula is basically what I got from youtube and manipulated into my situation.
Regards,
NickzNickz_BI
Solved! Go to Solution.
@NickzNickz_BI Hi, Look at the attached file, corrected the steps according to the example you provided.
@NickzNickz_BI Hi, Provide a more detailed condition under which you want to get the result.
Under the condition that you wrote the measure, the measure will be as follows
Status_Colum =
SWITCH(
TRUE(),
AND([Archievement] >= 0 , [Archievement] < [Thereshold]) , "Below Threshold",
AND([Archievement] >= [Thereshold], [Archievement] < [Min]), "Threshold",
AND( [Archievement] >= [Min], [Archievement] < [Target]) , "Min",
[Archievement] >= [Target], "Target" ,"Not Started"
)
Status_Measure =
SWITCH (
TRUE (),
AND (
SUM ( 'tbl_kpi_submission'[Archievement] ) >= 0,
SUM ( 'tbl_kpi_submission'[Archievement] )
< SUM ( 'tbl_kpi_submission'[Thereshold] )
), "Below Threshold",
AND (
SUM ( 'tbl_kpi_submission'[Archievement] )
>= SUM ( 'tbl_kpi_submission'[Thereshold] ),
SUM ( 'tbl_kpi_submission'[Archievement] ) < SUM ( 'tbl_kpi_submission'[Min] )
), "Threshold",
AND (
SUM ( 'tbl_kpi_submission'[Archievement] ) >= SUM ( 'tbl_kpi_submission'[Min] ),
SUM ( 'tbl_kpi_submission'[Archievement] )
< SUM ( 'tbl_kpi_submission'[Target] )
), "Min",
SUM ( 'tbl_kpi_submission'[Archievement] )
>= SUM ( 'tbl_kpi_submission'[Target] ), "Target",
"Not Started"
)
Hi @DimaMD ,
This is my further explanation as requested.
Current result:
The result should be like this:
Based on the input in the Trend column, it will determine the formula that should be used to produce the correct result. If column Trend is Ascending then run the formula for Ascending or else run the formula for descending.
Regards,
NickzNickz
@NickzNickz_BI Hi, Look at the attached file, corrected the steps according to the example you provided.
Dear @DimaMD ,
Hi ... I already tested both ... Thank you so much for the solution given. The only thing rite now is for me to understand on the coding side... 😁
Btw, instead of we create the trend column, can DAX read the trend of the value wether ascending or descending an immediately know how to calculate or respond to it.
Regards,
NickzNickz_BI
Hi @NickzNickz_BI I don't quite understand what you mean
"Btw, instead of we create the trend column, can DAX read the trend of the value wether ascending or descending an immediately know how to calculate or respond to it."
What I mean is, if the trend of value for threshold , min and target is descending then the result should be like item no 5 as above or else ....
But is ok ....
Btw below is my test result.
I just noticed that for Status_Column, some results are wrong compared to Status Measure... If my achievement is more that target, it show differently but it is ok since I still can use measure to populate the result... Thanks again. 👍
Regards.
NickzNickz_BI
@NickzNickz_BI Sorry I made a mistake,
Calculate colum :
Status_Colum =
SWITCH (
TRUE (),
AND ( [Archievement] > 0, [Archievement] < [Thereshold] ), "Below Threshold",
AND ( [Archievement] >= [Thereshold], [Archievement] < [Min] ), "Threshold",
AND ( [Archievement] >= [Min], [Archievement] < [Target] ), "Min",
[Archievement] >= [Target], "Target",
"Not Started"
)
Hi @DimaMD ,
I run a few more testing with diferrent value for item #4 which is descending order.
Below is my test script output.
Test Script #1
Expected Output : Min
Test Script Output : Below Threshold
Test Script #2
Expected Output : Below Threshold
Test Script Output : Target
Test Script #3
Expected Output : Min
Test Script Output : Below Threshold
Test Script #4
Expected Output : Target
Test Script Output : Target
Based on above test script, still got problem to calculate the result for descending order...
Regards,
NickzNickz_BI
Hi @NickzNickz_BI
Test Script #1
Explain why it should be "Min"
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |