Forum Discussion
Helper IIAdd "Total" on same level as current calculated dataset for line chart?
Resolver IIThere is no easy way to do that in Power BI unfortunately. Whenever you add something to the Legend, Power BI does not allow you to add other measures to the axis pane.
One workaround that has been used often is the following:
1. Create 1 Measure for each of your countries:
Country A = CALCULATE(SUM('Table'[Value]), 'Table'[Country] = "A")Country B = CALCULATE(SUM('Table'[Value]), 'Table'[Country] = "B")Etc.
2. Create 1 Measure for your total:
Total Countries Average = SUM('Table'[Value]) / DISTINCTCOUNT('Table'[Country])
3. Add all the Measures to the "Y-Axis" pane of your line chart.
4. On the formatting tab, go to "Lines", select your Total measure and apply Dotted shape.
It would look like this:
This is far from ideal solution but works as a workaround. I'll be happy if you find any better solution and post it here, but for the moment you can use that one.
Helper IIHi Ale
This solved the problem of getting the countries and Total average combined into one chart. Thank you!
The only problem with this solution is that it has lost the "connection" to the other visuals and filters. 😞
Except that, this was exactly what I was looking for.
- Ale
Hi nbrandborg ,
yes, that's the downside of using this workaround. However, as far as I know, there is no other way to do that in PBI.
- visheshjain
Impactful Individual
Hi nbrandborg,
Please can you check the 'Edit Interactions' from the Modelling pane in the ribbon, that they have not been turned off for the slicer and visual to interact.
Alternatively, you can check the measure as well in case you have used ALL() or some iteration of the ALL() function to ignore the slicers somehow.
I checked it with my reports and the slicers are working alright.
Regarding the X-axis, please post pictures showing the difference that you are talking about, as I am still not able to understand the problem there.
If my response helped you solve the problem, please kudo or or mark it as a solution so that others can reach the solution faster.
Thank you,
Vishesh Jain.
- nbrandborg
Hi visheshjain
Here's the picture showing the difference in the width of the x-axis which are dynamic in terms of how much space the legend takes on the left. Right now the two line charts (one with all the countries and the Total Monthly Weighted Average) are overlayed on top of each other.
Here's first a picture with all the countries selected:
And here's the chart with only "USA" selected.As you can see the "Line chart area" becomes bigger when a country with few characters are selected and the overlay doesn't match up anymore on the left side of the chart.
Ideally we could lock the Line chart or Legend area, as the overlay solution would be a good workaround to solve the issue.- Ale
One thing you could try is to change the legend's position. Try having it above or below the chart, so the X-Axis scale won't change.
Another solution would be to have the legends in a different visual. Create a chart with the same legends but make it transparent (or hide it behind the original one). So, when you select only 1 country, the selection it will affect only the "background" chart axis, not the original one.
