Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to create a clustered column chart with sum from multiple datasets

Hello,   I have been at this for a couple hours.   Dataset A has expenses in dollars by category with dates. Dataset B has budgets in dollars by category with dates.   The categories are mostly...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Ideally you should append the two datasets (Give a name to this appended dataset as Data).  Ensure that there is column in the Appended dataset which says "Budget" or "Actual" to identify the information in that row.  Then create a Calendar Table and build a relationship from the Date column of the appended dataset to the Date column of the Calendar Table. In the Calendar Table, extract Year and Month by using the following calculated column formulas: Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").

     

    Write these measures

     

    Actual = CALCULATE(SUM(Data[Amount]),Data[Type]="Actual")

    Budget = CALCULATE(SUM(Data[Amount]),Data[Type]="Budget")

     

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

Ideally you should append the two datasets (Give a name to this appended dataset as Data).  Ensure that there is column in the Appended dataset which says "Budget" or "Actual" to identify the information in that row.  Then create a Calendar Table and build a relationship from the Date column of the appended dataset to the Date column of the Calendar Table. In the Calendar Table, extract Year and Month by using the following calculated column formulas: Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").

 

Write these measures

 

Actual = CALCULATE(SUM(Data[Amount]),Data[Type]="Actual")

Budget = CALCULATE(SUM(Data[Amount]),Data[Type]="Budget")

 

Hope this helps.