Issue with DAX divide formula & card showing a true 0% calculation

[Mike35]

Hi,

I'm having issues with my DAX formula in showing a true 0% when the division formula calculates it. Using a Card (New) so as to be able to show if no data is present and therefore blank, then "No data" is displayed however, when my divide formula below calculates 0%, it shows as "No data" also. However in this instance, I want to be able to show as a 0% result as it demonstrates a 0% score rather than just no data being available.

Is there a way I can do this?

DAX formula below:

Score = 
DIVIDE(
    CALCULATE(
        COUNTROWS(Data),
        Data[Score] = "FC" || Data[Score] = "CID"
    ),
   CALCULATE(
    COUNTROWS(Data),
    Data[Score] = "FC" || Data[Score] = "CID" || Data[Score] = "NC")
)

Thanks

[parry2k · Accepted solution]

@Mike35 it basically depends on how you want to handle blanks for numerator and denominator:

I added two sample measures: what happens if the numerator or denominator is blank, when it returns 0, and when it returns blank. Also, I updated your measure to showcase what you can do to overcome this. You can tweak it further based on your needs since now you know why you are getting blank.

Num Blank = DIVIDE ( BLANK (), 1, 0 )

Den Blank = DIVIDE ( 1, BLANK (), 0 )

Score % = 
DIVIDE(
    COALESCE ( 
        CALCULATE(
            COUNTROWS(Data),
            Data[Score] = "FC" || Data[Score] = "CID"
        ),
        0
    ),
   CALCULATE(
    COUNTROWS(Data),
    Data[Score] = "FC" || Data[Score] = "CID" || Data[Score] = "NC")
    
) 

[Mike35]

Thanks @parry2k , appears to work well. Hopefully problem solved.

[parry2k]

@Mike35 Thanks for sharing the sample data. Do you have flat single table that you are working on or do you have other tables in your model? It is important to know the semantic model before I provide a solution.

[Ahmedx]

so what does this show?

Score = 
DIVIDE(
    CALCULATE(
        COUNTROWS(Data),
        Data[Score] = "FC" || Data[Score] = "CID"
    ),
   CALCULATE(
    COUNTROWS(Data),
    Data[Score] = "FC" || Data[Score] = "CID" || Data[Score] = "NC"),
0
)

[Mike35]

Still shows as "No data" as if it is a blank 

[parry2k]

@Mike35 can you share the sample data and explain the issue? It will help to provide the appropriate solution. 

[Mike35]

By way of an example, the formula is treating FC & CID results as good results, NC as bad results. Using the formula in my first post, from the below data I'd expect:

January: 50.00%

February: 0.00%

March: 100%

April: No data

1st January	CID
1st January	NC
1st February	NC
1st March	FC
1st March	FC
1st March	CID