Forum Discussion

ermuelle's avatar
ermuelle
Regular Visitor
8 years ago
Solved

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...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

I would probably use something like:

 

AVERAGEX(UNION(Table1,Table2),[Score])
  • ermuelle's avatar
    ermuelle
    Regular Visitor
    8 years ago

    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's avatar
      Ashish_Mathur
      Super User
      8 years ago

      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's avatar
        ermuelle
        Regular Visitor
        8 years ago

        Thank you! I think I found a solution.

  • Barrizm's avatar
    Barrizm
    New Member
    2 years ago

    Just what I was looking for.

    thanks