Multiple variable in a measure returns blank or zero

Question

I have multiple variable (Calculated) in a measure and giving me a blank result. let say I have 4 variable inside the measure and one of then has a null value or zero. The formula is to multiply all 4 variable and this is what I applied in my measure to removed the null or blank by adding COALESCE to variable. The problem with this code , if all of then are null or zero returns 1 and converted to 100.00%

Is there any idea or suggestion to simplify or fix this kind of scenario.

Sample

 Measure (Yield %) =
    VAR _p1 = DIVIDE(table_measure[Pass], PY_Measures[Pass_Input], 0)   
    VAR _p2 = DIVIDE(table_measure[Fail], PY_Measures[Fail_Input ], 0)  
    VAR _p3 =  DIVIDE(table_measure[Good],  PY_Measures[Good_Input], 0)  
    VAR _p4  =  DIVIDE(table_measure[Bad], PY_Measures[Bad_Input], 0) 
 RETURN
    (COALESCE(_p1,1) * COALESCE(_p2,1) * COALESCE(_p3,1) * COALESCE(_p4,1))

Data var---output p1----1 p2----96.63% p3----93.39% p4----92.81%

Desired result Measure (Yield%) 83.75%

PY_Measures is a folder that store all the measures I created. Here is the sample of measure I created for where the _P1 variable comes from(output)

Pass_Input = Calculate(PY_Measures(touched), filter(PY,(PY[Category]='WB') && (PY[type='ALL')))
  

 Pass = Calculate(PY_Measures(touched), filter(PY,(PY[Category]='WB') && (PY[type_category='Pass')))

Thank you in Advance!

Pass_Input = Calculate(PY_Measures(touched), filter(PY,(PY[Category]='WB') && (PY[type='ALL'))) Pass = Calculate(PY_Measures(touched), filter(PY,(PY[Category]='WB') && (PY[type_category='Pass'))) — Jon
– Jon, Commented Oct 15, 2023 at 10:29

Marcus · Accepted Answer · 2023-10-18 12:30:46Z

0

Just check if the sum of your variables is larger than zero:

Stuff =
VAR _p1 = DIVIDE ( table_measure[Pass], PY_Measures[Pass_Input], 0 )
VAR _p2 = DIVIDE ( table_measure[Fail], PY_Measures[Fail_Input ], 0 )
VAR _p3 = DIVIDE ( table_measure[Good], PY_Measures[Good_Input], 0 )
VAR _p4 = DIVIDE ( table_measure[Bad], PY_Measures[Bad_Input], 0 )
RETURN
    IF (
        ( _p1 + _p2 + _p3 + _p4 ) > 0,
        COALESCE ( _p1, 1 ) * COALESCE ( _p2, 1 ) * COALESCE ( _p3, 1 ) * COALESCE ( _p4, 1 ),
        0
    )

answered Oct 18, 2023 at 12:30

Marcus

4,0251 gold badge7 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple variable in a measure returns blank or zero

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related