Measure showing value for periods without data

[olimilo]

We're computing for the Defect Free % of our reports. Our data only goes as far as the current month but with the way the formula is structured (1 - (Reports with Defects / All Reports) ), the periods where there are no actual data pop up in our graph and show up as 100%:

Is there a way I can get this to compute only for the periods where there is actual data? Oddly enough, a blank entry also appears:

Whereas there shouldn't be any since I made the necessary filters for the page:

QAIssues is a custom column computed like so:

QAIssues = 
    COUNTROWS(
        RELATEDTABLE('Defects')
    )

~Defect % = 
    VAR cntDefects =
        COUNTROWS(
            FILTER('Audits', AND(NOT(ISBLANK('Audits'[QAIssues])), 'Audits'[QAIssues] > 0))
        )
    
    RETURN (1 - DIVIDE(cntDefects, COUNTROWS('Audits')))

[KarinSzilagyi · Accepted solution]

Your last bit after the return 

RETURN (1 - DIVIDE(cntDefects, COUNTROWS('Audits')))

is evaluated to 1-blank(), which PowerBI translates to 1-0 = 1 => 100% 

You can test this directly with

Minus Blank() = 1-BLANK()

which also returns 1.

If you want those entries to disappear from your visual, you’ll need to add a check before the subtraction and return BLANK() whenever the denominator is empty.

For example:

~Defect % =
VAR cntDefects =
    COUNTROWS (
        FILTER (
            'Audits',
            NOT ( ISBLANK ( 'Audits'[QAIssues] ) )
                && 'Audits'[QAIssues] > 0
        )
    )
VAR cntAll = COUNTROWS ( 'Audits' )
RETURN
    IF ( cntAll = 0, BLANK(), 1 - DIVIDE ( cntDefects, cntAll ) )

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[rohit1991]

Hi @olimilo 

Adjust your measure so it only calculates when there are audits:

Defect Free % =
VAR denom   = [Audits]
VAR defects = [Audits with Defects]
RETURN
   IF ( denom = 0, BLANK(), DIVIDE ( denom - defects, denom ) )

If you prefer to explicitly show 0% in months without audits, change it slightly:

Defect Free % (zero on no-audit) =
VAR denom   = [Audits]
VAR defects = [Audits with Defects]
RETURN
   IF ( denom = 0, 0, DIVIDE ( denom - defects, denom ) )

• Use a proper Date table and relate it to your audit data.
• Put Month from the Date table on the axis, set the X-axis to Continuous.
• If you want to hide empty months altogether, add a visual filter [Audits] > 0.