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

 

 

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

     

     

3 Replies

  • 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.

     

     

  • KarinSzilagyi's avatar
    KarinSzilagyi
    Icon for Super User rankSuper User
    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 ) )