I have 2 columns in a table UPS as follows

Location    1st Service Age  2nd Service Age
town1       3                0
town2       5                2
town3       6                0
town4       0                0
town5       2                1

How can I calculate (measure) the average age of both columns, ignoring 0's and slicer. For the above the answer should be 19/6 = 3.17, if all zeros, give 0.00

Thank you in advance

I have 2 columns in a table UPS as follows

Location    1st Service Age  2nd Service Age
town1       3                0
town2       5                2
town3       6                0
town4       0                0
town5       2                1

How can I calculate (measure) the average age of both columns, ignoring 0's and slicer. For the above the answer should be 19/6 = 3.17, if all zeros, give 0.00

Thank you in advance

• powerbi

• I don't see how you get 19/6. I see the 10 integers sum to 19 which no doubt is the numerator coming from from 2 columns and 5 rows, but I don't see the route to a divisor of 6. – pgSystemTester Commented Mar 29 at 16:26
• 1 the divisor is coming from the non-zero values (3, 5, 6, 2, 2, 1) from both the columns – cghanta Commented Mar 29 at 16:33

1 Answer 1

There's quite a few ways to do this, but here's probably the easiest to follow. You'll need three calculations against your table. These could all be conducted in one measure or broken out individually, as shown below:

Total_Sum

Total_Sum = SUM('UPS'[1st Service Age]) + SUM('UPS'[2nd Service Age])

Non_Zero_Count

Non_Zero_Count = 
SUMX(
    'UPS',
    IF('UPS'[1st Service Age] = 0, 0, 1) + IF('UPS'[2nd Service Age] =0, 0, 1)
)

AVG_AGE

AVG_AGE = if([Non_Zero_Count]=0,0,
    DIVIDE([Total_Sum], [Non_Zero_Count]))

or combined to single measure

singleCalc = 
VAR Total_Sum = SUM('UPS'[1st Service Age]) + SUM('UPS'[2nd Service Age])
VAR Non_Zero_Count = 
SUMX(
    'UPS',
    IF('UPS'[1st Service Age] = 0, 0, 1) + IF('UPS'[2nd Service Age] =0, 0, 1)
)
RETURN
if(Non_Zero_Count=0,0,Total_Sum/Non_Zero_Count)