Calculating average over multiple tables

[ermuelle]

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

[Ashish_Mathur]

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.

[Greg_Deckler]

I would probably use something like:

AVERAGEX(UNION(Table1,Table2),[Score])

[ermuelle]

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.

[Ashish_Mathur · Accepted solution]

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.

[ermuelle]

Thank you! I think I found a solution.

[yasminsalah]

Hi!

I know it's been ages since this post. But would you happen to have the pbix file? I have almost the exact same question

[v-chuncz-msft]

@ermuelle,

By the way, you may help accept solution. Your contribution is highly appreciated.

[Ashish_Mathur]

You are welcome.