Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Measure showing value for periods without data

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 period...
Need Help
  • KarinSzilagyi's avatar
    KarinSzilagyi
    1 year ago

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

     

     

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

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.