Let's imagine you have a bar chart with the % of people that likes smashed potatoes by state. Something like this (fake data, off course):
But you need to include another bar that shows the % for the whole country. There are a lot of ways to do that, going to show my favorite.
The main idea here is to create another table, let's call it <<Location>>, with the states plus USA. Than you create another table, <<StatesLocation>>, that relates then in a way that USA relates to all states and each states relates to itself. For example, Alabama in <<Location>>, relates to Alabama in <<States>>. USA relates to Alabama and all the other 50 states. You need the <<StatesLocation>> table because each state is going to show twice, once linked to itself and once linked to USA.
The model will look like this:
You can build Location and StatesLocation tables in various ways, it did it using PowerQuery. It is prety straightforward, but it is all on the pbix file attached.
In the chart, instead of using the column from <<States>>, you use the column from <<Location>>. I also create a order column in location, to show USA first. And finally, I create a measure to paint USA using a different color from the states.
This is the final result:
Below is the pbix with the example.
Pretty simple, right?
Comments