Measure shows blank instead of 0

[o59393]

Hi all

I have created a measure that divides the total samples equal or above 1.33 by the total samples above 0

The measure works well excepts when you have 0 samples equal or above 1.33

For example for a value of 0.08 in brix the correct result should be: 

0 samples equal or above 1.33 / 1 sample  = 0%

 The measures I use are:

cpk = [cpk count greater than 1.33]/[cpk count]

cpk count greater than 1.33 = CALCULATE(COUNTA(Cpk[Value]),Cpk[Value]>="1.33")

cpk count = CALCULATE(COUNTA(Cpk[Value]),Cpk[Value]>="0")

The dasboard shows blank instead of 0%

Any idea how to get it right?

Thanks!

[JasonC_XBI · Accepted solution]

It should be just a case of adding "+0" to the end of your formula, so that blanks are replaced by "0"

e.g. 

cpk count greater than 1.33 = CALCULATE(COUNTA(Cpk[Value]),Cpk[Value]>="1.33")+0

[amitchandak]

@o59393 , have already tried like

cpk = divide([cpk count greater than 1.33],[cpk count],0)

[o59393]

Hi @amitchandak 

Still not showing the 0%:

Regards.

[amitchandak]

@o59393 , I checked file, as brix do not have data for the current setting you are getting null, +0 in old formula ([CPK]+0)a formula like this can work

CPK for brix =
CALCULATE(if(max('CPK'[attribute]) IN { "brix" },[CPK], 0))

[o59393]

Hi @amitchandak 

I still got 0's for all the rows:

I did the following to solve it:

cpk = divide([cpk count greater than 1.33],[cpk count])

cpk count greater than 1.33 = CALCULATE(COUNTA(Cpk[Value]),Cpk[Value]>="1.33")+0

Regards!

[mahoneypat]

How about this approach?  Your calculation is still not clear (e.g., what is brix?), but this approach of breaking apart the numerator and denominator and applying logic to either both, before the division step may get you there.

cpk = var numerator = [cpk greater than 1.33]

var denominator = [cpk count]

return if(numerator<1,0, DIVIDE(numerator, denominator))

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[o59393]

Hi  @mahoneypat 

It didnt work

Please see the attached pbix, in the example the cpk brix column shows blank but should be 0%.

Please notice the 0% should be only in line_id #3 since it's the only one with a value for brix (right table contains all the values).

Therefore the rest of line_id's should be blank in the left table:

Pbix

https://1drv.ms/u/s!ApgeWwGTKtFdhmc6hMVmmqyeKKwZ?e=GZxCCq

Thanks!

[HotChilli]

Add a zero ( + 0) or use the new COALESCE function e.g. COALESCE(measure,0)

[o59393]

hi!

Now I got all of the blanks filled with 0%'s

How can I just make appear 0% in those cases where the numerator is 0 samples (does not equal or above to 1.33) and the denominator is above 0 samples?

Thanks!