Forum Discussion
Calculating the Average per Row
Hello, I would like to calculate the average per row without including blank cells or 0s in my calculations. The table below gives a good example of the data I'm working on.
| Name | Jan | Feb | March | April | May | Total Average |
| A | 2 | 0 | 6 | 4 | ||
| B | 3 | 6 | 1 | 2 | 1 | 2.6 |
| C | 2 | 3 | 2.5 |
I essentially want to sum up the columns between Jan and May and get the average per row. The row for A should calculate (2 + 6) / 2 = 4 while the row for B should calculate (3 + 6 + 1 + 2 + 1) / 5. And this should be done for every row. I hope my question makes sense and I would really appreciate any help. Thank you.
Hi Anonymous ,
What's the data structure of your data?
If it is like below, please check the attachment whether help.
If it is just like below, please try to UnPivot columns first. And then, refer to the attachment.
Name Jan Feb March April May A 2 0 6 B 3 6 1 2 1 C 2 3 Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- FrankAT
Community Champion
Hi Anonymous
here is my solution with a table visual:
Measure = VAR _Sum = SUM('Table'[Value]) VAR _CountGreaterNull = CALCULATE(COUNT('Table'[Value]),'Table'[Value] <> BLANK() || 'Table'[Value] > 0) RETURN DIVIDE(_Sum,_CountGreaterNull)Regards FrankAT
Hi FrankAT Thank you for your solution. This works for columns but how could I do this for rows? I want to sum the rows some which may be empty.
- Icey
Community Support
Hi Anonymous ,
What's the data structure of your data?
If it is like below, please check the attachment whether help.
If it is just like below, please try to UnPivot columns first. And then, refer to the attachment.
Name Jan Feb March April May A 2 0 6 B 3 6 1 2 1 C 2 3 Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
