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 am looking for a way to create a stacked bar chart with a target line. The target line has to be dynamic depending on the filter criteria selected.
At the moment, I have two different tables: The first one contains the actual inventory data per Week and company location. The second table contains the inventory target per company location. This goal is the same for every week. How do I combine these two tables into a graph with bars (the actual inventory per week) and a line (inventory target, same for every week)? The x-axis is supposed to display the months and the y-axix the inventory value. It has to be possible to filter/slice by company location and then the visual needs to show the target for that specific location.
Thanks in advance.
Alina
Solved! Go to Solution.
@AlinaJa Maybe you could post photo or sample pbix? I don't understand what isn't working.
That is what I get when I use your sample data, with Target[Location] in the slicer, and Target[Location] in the column series of the chart (though maybe not needed).
If no location is selected, the target will add up to sum of all locations, but when location is selected it behaves as expected.
Is that what you're looking for?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy The problem is that if I add the column from my target table as line values, it always gives me the sum of all targets. No matter which location I select.
If I add the target as a column to the other table and then add it to the graph as the line value, I have to choose whether I want to use the average/sum/Min/Max/... But I don't want any of these, I just want the actual value for this location.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy I set up a Many:1 relationship between the two tables by using the Location Column. I posted Example Data above
Which Location are you using in the line chart? You need to use the Location from the Target table. You will still need to use an aggregation, but if your table is built properly it won't matter if you use SUM/Avg/max as they'll all give the same result.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
The one from the target table. It just shows the total target for all locations, which doesn't change when I select a location @AllisonKennedy
@AlinaJa Maybe you could post photo or sample pbix? I don't understand what isn't working.
That is what I get when I use your sample data, with Target[Location] in the slicer, and Target[Location] in the column series of the chart (though maybe not needed).
If no location is selected, the target will add up to sum of all locations, but when location is selected it behaves as expected.
Is that what you're looking for?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
That's what I was missing! I used the location from the wrong table!
Thank you so much @AllisonKennedy
@AlinaJa , you can use analytics pane
https://docs.microsoft.com/en-us/power-bi/desktop-analytics-pane
or you can create an avg measure with all or allselected and use line clustered visual
Thank you for you quick response. @amitchandak
I am quite new to the Power Bi topic.
What exactly do you mean by an avg measure with all or allselected? Could you give me an example how a measure like this could look like?
@AlinaJa , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Example
CALCULATE(Average(Sales[Sales Amount]),all(Sales))
CALCULATE(Average(Sales[Sales Amount]),allselected(Sales))
Month | Location | Inventory |
1 | Germany | 1000 |
1 | US | 2000 |
1 | China | 2000 |
2 | Germany | 1500 |
2 | US | 1000 |
2 | China | 1000 |
3 | Germany | 1300 |
3 | US | 2500 |
3 | China | 2000 |
Location | Target |
Germany | 1000 |
US | 1500 |
China | 1500 |
I made up some sample data to simplyfy it. @amitchandak
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |