Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Proud to be a Datanaut!
Private message me for consulting or training needs.
Solved! Go to Solution.
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
Proud to be a Super User!
Hi @negi007 ,
I couldn't see how to attach the data here, so I placed it at this link:
The file is called "SampleData.xlsx"
Proud to be a Datanaut!
Private message me for consulting or training needs.
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
Proud to be a Super User!
Hi @negi007 ,
That is exactly what I was trying to do!! Thanks so much!
Proud to be a Datanaut!
Private message me for consulting or training needs.
Hi @negi007 ,
THANKS! I will try this in an hour or so and let you know!
Proud to be a Datanaut!
Private message me for consulting or training needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |