Forum Discussion

SaganBigRedDog's avatar
SaganBigRedDog
Helper I
9 years ago
Solved

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 Mo...
Need Help
  • SaganBigRedDog's avatar
    SaganBigRedDog
    9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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:

https://msdn.microsoft.com/en-us/library/jj677276.aspx

  • SaganBigRedDog's avatar
    SaganBigRedDog
    Helper I
    9 years ago

    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.

    • v-caliao-msft's avatar
      v-caliao-msft
      Microsoft Employee
      9 years ago

      SaganBigRedDog,

       

      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

      • SaganBigRedDog's avatar
        SaganBigRedDog
        Helper I
        9 years ago

        Hi Charlie,

         

        I get the same thing using this.  Here is what it looks like with the measures and disc % set to Disc % = DIVIDE([measure 1],[measure 2],0)