blank value instead of 0 and therefore average is not correct

[Laeti]

I have a number of batches and I want to get the average number of critical deviations per bacth.

I have a table with the batch number and another table with the deviations and the batches associated

I have created in the batch table the following column:

# critical deviation= CALCULATE(DISTINCTCOUNT('Dev raw data'[Event ID]),filter('Dev raw data','Dev raw data'[Key_Material_Batch]='Fact_ Batch Raw data'[Key_Material_Batch] && 'Dev raw data'[Level]="Critical"))

for most of my batches, I dont have a critical deviation so no value is returned

when I want to create a visual and show the average number of critical deviation by batch, it should consider the batches without critical deviation as having 0 deviation and not blank, therefore the average is overestimated

How can I get the average deviation considereing the 0 rather than blank?

Thanks

[harshnathani]

Hi @Laeti ,

You can use the following Calculated Column,

Calculated Column = If (NOT(ISBLANK(#CriticalDeviation), AVERAGE(#CriticalDeviation))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Laeti]

Hi!

It did not work but it helped me to find a solution. I did change the formula and it worked! Thanks

if(isblank(CALCULATE(DISTINCTCOUNT('Dev raw data'[Event ID]),filter('Dev raw data','Dev raw data'[Key_Material_Batch]='Fact_ Batch Raw data'[Key_Material_Batch]  && 'Dev raw data'[Level]="Minor"))),0,CALCULATE(DISTINCTCOUNT('Dev raw data'[Event ID]),filter('Dev raw data','Dev raw data'[Key_Material_Batch]='Fact_ Batch Raw data'[Key_Material_Batch]  && 'Dev raw data'[Level]="Minor")))

[harshnathani]

@Laeti ,

Great that the suggestion helped you find your own solution.

Cheers,

Harsh Nathani