Calculate function not filtering launched projects

[o59393]

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.

[TomMartens · Accepted solution]

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 - NULL

this 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

[o59393]

Nevermind, I got it 🙂

Thanks a mill for the help!!!

[o59393]

Hi Tom!

Thanks a lot for the reply. Can you share the pbix?

Thanks!