Forum Discussion
Showing zero values?
Hi!
So I have this chart (left)
And I want it to show 0% for the blank values.
The measure formula is:
Discrepancy Percent =
CALCULATE (
DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs])/DISTINCTCOUNT('! NEW ALL 401 OPEN Last Month'[Submission Bis IDs]))
and I tried adding +0 on the end but ended up with this (right)
How do I fix this?
Thanks, Charlie! I figured out the issue was with one of my tables with the FSS and District fields. I used your formula after correcting and it worked! Thanks!!
8 Replies
maybe do a check to see if its blank first? but i am not sure if that creates the same issue
test =
IF (
ISBLANK (
CALCULATE (
DISTINCTCOUNT ( '! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs] )
/ DISTINCTCOUNT ( '! NEW ALL 401 OPEN Last Month'[Submission Bis IDs] )
)
),
0,
CALCULATE (
DISTINCTCOUNT ( '! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs] )
/ DISTINCTCOUNT ( '! NEW ALL 401 OPEN Last Month'[Submission Bis IDs] )
)
)Hi,
Yes, it created the same issue.
Technically, those blank records are "0%" values.
ANy idea how to resolve?
Instead of using the "/" operator to divide, try using DAX's DIVIDE(numerator, denominator, 0) in your existing formula. This will give you an actual zero-value instead of BLANK() if the denominator is zero.
For more information:
This didn't work either. I used:
Discrepancy Percent =
DIVIDE(
DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs]),DISTINCTCOUNT('! NEW ALL 401 OPEN Last Month'[Submission Bis IDs]),0)but it still shows as blank:
For District 11, for example, ALyssa has 0 discrepancies and 16 submissions. Should be 0/16. And should show as 0% on the report.
Could you please create two measures to test the result?
measure 1 = DISTINCTCOUNT('! NEW DISCREPANCY REPORT Last Month'[Discrepancy Bis IDs])
measure 2 = DISTINCTCOUNT('! NEW ALL 401 OPEN Last Month'[Submission Bis IDs])
Regards,
Charlie Liao
