I have Data like below, for which i need to calculate average.
Name | Year | Value |
Sales | 2023 | 0 |
Sales | 2023 | 10 |
Sales | 2023 | 0 |
Sales | 2023 | 15 |
Sales | 2024 | 0 |
Sales | 2024 | 150 |
Sales | 2024 | 0 |
Sales | 2024 | 300 |
Sales | 2025 | 50 |
Sales | 2025 | 0 |
Sales | 2025 | 0 |
Sales | 2025 | 100 |
For Example: If I want to calculate AVG for 2024 the calculation should only pick (150+300)/2=225 not (150+300+0+0)/4=112.5, Is there a way to achieve this for all the line items?
Solved! Go to Solution.
Achieved Using summarize average Ignore 0's option
To ignore the record in Summarize tool, value should be NULL. For example, replace the record which value is 0 with NULL, and go Summarize tool like this.
===========================================================
As a side note, this technique is also powerful when summarize string data. If some record are empty string and concatenate them, its result contains unwanted separator like
A,B,C,,E
However, by converting empty string to NULL, result will be like this format that should be preferable in most cases.
A,B,C,E