Stacked Column Chart with Actual Vs Budget (and third column for Last Year)

[collinq]

Hi all,

I have a problem that I can't seem to figure out.  In my query I have 3 sets of data.  I have the Material Margin, COGS and Gross Margin fields.  They are by date by Territory.  It is like this:

ALSO, I have a totally different table, again by Date and Territory that has the same data for the Budget.  And, I will have to do calculations to get previous year.  The goal is to have three separate columns in the Stacked column visual.  This is what the end goal should look like:

Using some Dax I was able to put the data from the ACTUAL table together (breaking them apart and appending them together in M didn't work but that is for another day).  

So, I now have this:

That is great, but now I need to add the other two columns.  I need it to look like this:

In the Stacked Column, I am apparently unable to add different values.  

Question number 1:  Is there a way to do this (and still drill down by date).  

Question number 2:  If not, and I have to build three separate stacked column visuals, how can I keep them in absolute sync where if I drill down on one all three will drill down to the exact same date level?

Thanks so much!

[negi007]

@collinq Will it be possible for you to share some dummy data in the same structure that you have. Will see if that can be done in powerbi. Looking forward to your response.

[collinq]

Hi @negi007 ,

I couldn't see how to attach the data here, so I placed it at this link:

https://pmps1-my.sharepoint.com/:x:/g/personal/cquiring_epmstrategy_com/ERdZxwv-gJ9Ht8MqlZ-bNLQBR0Kp...

The file is called "SampleData.xlsx"

[negi007 · Accepted solution]

@collinq 

You can do below steps. Aggreate your actuals and budget table at date level and have seperate columns for 

Material Margin $, 

Other COGS $

Gross Margin $

Category (Actuals or Budget)

1. Aggregate Actuals

2. Aggregate Budget

3. Append above two tables into one

4. Your visual would be like this. 

5. Since you wish to have balance (Budget-Actuals), you may create one more table to have that data and then append all threes

you can download my pbix file here

https://drive.google.com/file/d/1EFaNMfNSVPWXVf7oTWWosTD_SHA6Mr9s/view?usp=sharing

[collinq]

Hi @negi007 ,

That is exactly what I was trying to do!!  Thanks so much!

[collinq]

Hi @negi007 ,

THANKS!  I will try this in an hour or so and let you know!