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

[Anonymous]

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 the same.

Is it possible to make a clustered column chart that will sum these two dollar amounts, in order to visualize the deficit/surplus over a given amount of time?

I tried making a third dataset with just the category, but it would only have 1 active relationship to A or B.

Thank you,

Danny

[Ashish_Mathur · Accepted solution]

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.