powerbi - Calculate average of 2 columns ignoring zeros - Stack Overflow

admin管理员组

文章数量:1356241

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

Share Improve this question Follow edited Mar 29 at 16:27 DarkBee 15.5k88 gold badges7272 silver badges117117 bronze badges asked Mar 29 at 16:07 cghantacghanta 3733 bronze badges 2

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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)

本文标签： powerbiCalculate average of 2 columns ignoring zerosStack Overflow