Forum Discussion
Calculate function not filtering launched projects
Hi all !
I created a dax function (product kpi) to calculate the % of how good we are at launching projects. The rule is simple
1-(Real days-Theorical Days)/Theorical Days
It calcualtes it only for launched projects in the column Status.
The problem I have is that the metric is calculating me the % for non launched projects or that have a Real days cell empty.
As seen below:
When I select 2019 Q4, there are 3 projects. One already launched for manager ZZZ. Which is ok to reflect the % of the dax metric.
the manager AAA and XXX have on going projects, therefore the metric should return no % since it hasnt launched.
Can you please advise how to do that?
I attach excel and pbi.
https://www.mediafire.com/file/0zham95mumjpqw2/projects.pbix/file
https://www.mediafire.com/file/k3hsf9n2o4tvbad/query_%2813%29.xlsx/file
Thanks.
Hey o59393 ,
the reason why there is a percentage, is because the formula is reduced to this, when the status does not equal "launched":
1 - NULLthis leads to a percentage of 100% as NULL is coerced to 0 (zero)
I guess this provides what you are looking for:
Product KPI = var _percentage = (CALCULATE(SUM(Table_query__13[Tech_COMM_Days_Real]),Table_query__13[Status]="Launched")-CALCULATE(SUM(Table_query__13[Tech_COMM_Days_Gantt]),Table_query__13[Status]="Launched"))/CALCULATE(SUM(Table_query__13[Tech_COMM_Days_Gantt]),ABS(Table_query__13[Status]="Launched")) return IF( _percentage == BLANK() , BLANK() , 1 - _percentage )Here the result of the "calculation" is stored to the variable "_percentage".
Then using the strict operator == (indeed 2 equal signs) allows to check if the value of the variable is BLANK() meaning NULL.
Using this formula, the matrix visual just contains this:
Hopefully this is what you are looking for.
Regards,
Tom
3 Replies
Hey o59393 ,
the reason why there is a percentage, is because the formula is reduced to this, when the status does not equal "launched":
1 - NULLthis leads to a percentage of 100% as NULL is coerced to 0 (zero)
I guess this provides what you are looking for:
Product KPI = var _percentage = (CALCULATE(SUM(Table_query__13[Tech_COMM_Days_Real]),Table_query__13[Status]="Launched")-CALCULATE(SUM(Table_query__13[Tech_COMM_Days_Gantt]),Table_query__13[Status]="Launched"))/CALCULATE(SUM(Table_query__13[Tech_COMM_Days_Gantt]),ABS(Table_query__13[Status]="Launched")) return IF( _percentage == BLANK() , BLANK() , 1 - _percentage )Here the result of the "calculation" is stored to the variable "_percentage".
Then using the strict operator == (indeed 2 equal signs) allows to check if the value of the variable is BLANK() meaning NULL.
Using this formula, the matrix visual just contains this:
Hopefully this is what you are looking for.
Regards,
Tom
