- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculate Average of Averages

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculate Average of Averages

02-19-2016
05:53 AM

I want to get the average of a series of averages, but can't figure it out. Here's my data:

The circled value of 132.05 is the average of the total transactions / total workers (2509 / 19 = 132.05). But instead I need the average of the Average/Day *daily* values. In other words, it should be 29.52. How would I go about getting that? Thanks.

(BTW, I realize 19 is listed as the total # of workers even though every day shows only 17. I suspect at this point that is because the workers each day are not always the same, and therefore the total distinct count of workers across the 5 days is actually 19.)

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2016
06:51 AM

This is a great question - based on what you're saying you'll need to 1) figure out the average for each day and 2) then average those averages. In the future, it's helpful if you share your DAX in your question.

To do this you need to use the AVERAGEX function.

- The first argument is a table that you will iterate over all the rows - in your case you want a table of all the days. If you have a table that already has one row for every day then use that, otherwise you may want to use the VALUES function to ensure that you get just one row for every day.
- The second argument is the expression you want to calculate for every row of the table - in your case the average number of transactions for that day. For the second argument you may need to use CALCULATE depending on the expression.

The final result of the function is the average of the result from each row. This is the way that all the "X" functions work - SUMX, AVERAGEX, STDEVX, etc.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2016
06:51 AM

The AvergageX function should give you what you need, something like

AverageX(values(tablename[colname]),[Average/Day Measure])

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2016
06:51 AM

The AvergageX function should give you what you need, something like

AverageX(values(tablename[colname]),[Average/Day Measure])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2016
07:05 AM

Thanks to both of you. Very helpful. The VALUES function was the key as I had already been trying the AVERAGEX function without success. I had seen (and probably even used) the Values function before, but haven't spent enough time getting familiar with all the DAX functions.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2016
08:12 PM

The average of averages is usually the wrong way to approach this sort of problem. I'd rather take [Transactions] / [Worker-Days] for the top level average.

Let's imagine the following sample data:

Day | Workers | Transactions | Average/Day

1 | 1 | 10 | 10

2 | 2 | 40 | 20

The average of averages is (10 + 20) / 2 = 15. Transactions / Worker-Days is (10+40) / 3 = 16.7. Here we have 3 workers. 2 of them are able to process 20 transactions/day. 1 of them is only able to do 10/day. The 10/day worker is the outlier here.

Imagine adding a third row:

Day | Workers | Transactions | Average/Day

1 | 1 | 10 | 10

2 | 2 | 40 | 20

3 | 5 | 105 | 21

Average of averages: (10 + 20 + 21) / 3 = 17

Transactions / worker-days: (10 + 40 + 105) / (1 + 2 + 5) = 19.4

Here, it's clear that the typical worker gets ~20 transactions / day, but the average of averages method understates average productivity by >10%.

Average of averages overstates the impact of outliers.

To get transactions over worker-days you can simply replace your [Average/Day] measure:

// DAX // Measure Worker-Days = COUNTROWS( SUMMARIZE( 'FactTransaction' ,'FactTransaction'[WorkerKey] ,'FactTransaction'[Date] ) ) Average/Day = DIVIDE( [Transactions], [Worker-Days] )

SUMMARIZE() groups by the fields named, navigating relationships from the base table identified in the first argument. Thus, it gives us a table made up of the unique combinations of workers and days. When a single day is in context (one of your detail rows from the sample in your original post), this will be the same as the distinct number of workers on that day. [Average/Day] is thus the same for the detail level.

At the total level, we use the appropriate numerator and denominator and get a much more accurate result.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2016
06:51 AM

This is a great question - based on what you're saying you'll need to 1) figure out the average for each day and 2) then average those averages. In the future, it's helpful if you share your DAX in your question.

To do this you need to use the AVERAGEX function.

- The first argument is a table that you will iterate over all the rows - in your case you want a table of all the days. If you have a table that already has one row for every day then use that, otherwise you may want to use the VALUES function to ensure that you get just one row for every day.
- The second argument is the expression you want to calculate for every row of the table - in your case the average number of transactions for that day. For the second argument you may need to use CALCULATE depending on the expression.

The final result of the function is the average of the result from each row. This is the way that all the "X" functions work - SUMX, AVERAGEX, STDEVX, etc.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

Announcements

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Check out the April 2024 Power BI update to learn about new features.

Featured Topics

Top Solution Authors

User | Count |
---|---|

117 | |

102 | |

69 | |

67 | |

43 |

Top Kudoed Authors

User | Count |
---|---|

151 | |

103 | |

102 | |

87 | |

63 |