Forum Discussion
Calculating average over multiple tables
I am trying to calculate a total monthly average from data in two seperate tables. This seems like it should be easy, but I cannot figure out the proper DAX formula. Please help! Here's what the data looks like. How do I connect these 2 tables so that I can calculate the overall monthly average of both managed and referred satisfaction? Basically I want a line chart that has the average managed satisfaction, the average referred satisfaction and then the total satisfaction by month. Thank you in advance.
Table 1 - Managed Satisfaction
Date Score
July 1, 2017 3
July 2, 2017 4
August 28, 2017 2
Table 1 - Referred Satisfaction
Date Score
July 14, 2017 5
August 3, 2017 1
Hi,
You must create a calendar table and create a relationship from the Date column of the appended table to the date column of the Calendar Table. In the Calendar Table use the =FORMAT(Calendar[Date],"mmmm") formula to extract the month name from the Date and =YEAR(Calendar[Date]) to extract the Year. In your visual, drag the Year and Month from the Calendar Table.
Hope this helps.
9 Replies
I would probably use something like:
AVERAGEX(UNION(Table1,Table2),[Score])
Thanks. I've tried this, but I only get the overall average across the time period. I can't look at it by month. I want to be able to look at the total average (referred + managed satisfaction) by month.
Hi,
You must create a calendar table and create a relationship from the Date column of the appended table to the date column of the Calendar Table. In the Calendar Table use the =FORMAT(Calendar[Date],"mmmm") formula to extract the month name from the Date and =YEAR(Calendar[Date]) to extract the Year. In your visual, drag the Year and Month from the Calendar Table.
Hope this helps.
Just what I was looking for.
thanks
Hi,
In each table, create another column titled Type of satisfaction. Using Query Editor, append both Tables and then create your desired visual with this simple measure
=SUM(Data[Score])
Hope this helps.
