Calculating Average Excluding Zero Rows
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Achieved Using summarize average Ignore 0's option
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
