Measure to Give 0% and N/A or Blank

[KMcCarthy9]

I have a measure that divides the number of "Yes" by the sum of "Yes" + "No." I added "+0" at the end of the calc in order for 0% to show as a result, however I have some cases that should truly be blank and not 0%. How do I write a measure that will give me 0% when the actual calc results in 0 and a blank or N/A when the calc results in blanks (no values)? 

Sample .pbix file here: .pbix 
In this file, Vendor 1 has 23 records in the SDR Yes / No column, all equal to 'No'. 0/23 = 0, however, it is displayed blank
Provider 2 has 1 record in SDR Yes / No, equaling 'Exclude'. This should remain as shown blank.

[dedelman_clng]

Hi @KMcCarthy9  - 

Without knowing more about your measures, this is an educated guess, but you may want to consider adding the +0 to the numerator only, not the entire measure.  This way if there are records for the denominator, you should get 0, and if there are no rows for the denominator you should continue to get blank.

Hope this helps

David

[KMcCarthy9]

Hi @dedelman_clng, unfortunately that did not work. 
I have added a test .pbix file here: .pbix 

In this file Vendor 1 has 23 records under column SDR Yes/No, all equaling 'No'. 0/23 = 0, yet it is shown as blank
Vendor 2 has 1 record under SDR Yes/No, equaling 'Exclude'. This should remain as showing as blank. 

Thoughts?

[dedelman_clng · Accepted solution]

Hi @KMcCarthy9 - use the DIVIDE function, and add 0 to the numerator

SDR2 =
DIVIDE (
    CALCULATE ( COUNTA ( 'data'[SDR Yes/No] ), 'data'[SDR Yes/No] IN { "Yes" } ) + 0,
    CALCULATE (
        COUNTA ( 'data'[SDR Yes/No] ),
        'data'[SDR Yes/No] IN { "Yes", "No" }
    )
)

Hope this helps

David

[KMcCarthy9]

@dedelman_clng Thank you so much! I've spent all day on this problem. 
I'm not very good with DAX yet, but do you know the reason why the divide function works differently than just using '/'? That is the only thing different in the working DAX (as I did try 0 with the numerator on my original DAX and got very different results). 

Thanks! 

[dedelman_clng]

Hi @KMcCarthy9  - DIVIDE has a 3rd argument (which we didn't use) that is the value to return if the denominator is 0. The default is BLANK() so I just didn't put it in the code.  Standard division gives the NaN error if the denominator is 0.

David