Hi allI need some help. I have 15 countries in a line chart - it's busy I know, but to accommendate for this we would need a weighted monthly average as a highligted trendline. I have constructed a new table with the Total data that sums from the country table - but I somehow need this to be a variable on the same level as the countries from the original dataset. Can this be done?I have illustrated here what I would like to achive with the pink dotted line.Right now it's simply two line charts on top of each other. The problem being is that the line chart with countries are dynamic in it's form, so it only USA is selected for example, then the "graph area" becomes bigger and the overlay doesn't fit anymore on the month to month.Is it somehow possible to add this Total line to be seen as a "country" on the same level? Ideas and suggestions would be highly appreciated!

There 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.

Hi nbrandborg , Not sure if I understood your problem and it would really help if you could share some sample file. From what I understood, I think you can use the 'small multiples' in the line chart to separate every single country. If you do not want the scale of your graph to change, when selecting a country, you can provide the upperlimit of the Y-axis, instead of keeping it 'Auto', from the formatting pane, which will make the scale static. Again apolgies, if my response is not what you are looking for and in that case if you could please provide more information. Thank you, Vishesh Jain

Hi AleThis 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.

Hi visheshjain The problem is not the Y-axis as I have locked it to show 0-100% always - but the width of the graph area - the X-axis. When a country, like USA, is selected as a single-selection, then the line-chart area becomes wider, as the legend takes up less space on the left. I have too many countries to have the legend in the top or bottom.

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.

Add "Total" on same level as current calculated dataset for line chart?

11 Replies

Ale
Resolver II
4 years ago
There 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.
- nbrandborg
  Helper II
  4 years ago
  Hi 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
    Resolver II
    4 years ago
    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
4 years ago
Hi nbrandborg ,

Not sure if I understood your problem and it would really help if you could share some sample file.

From what I understood, I think you can use the 'small multiples' in the line chart to separate every single country.

If you do not want the scale of your graph to change, when selecting a country, you can provide the upperlimit of the Y-axis, instead of keeping it 'Auto', from the formatting pane, which will make the scale static.

Again apolgies, if my response is not what you are looking for and in that case if you could please provide more information.

Thank you,

Vishesh Jain
- nbrandborg
  Helper II
  4 years ago
  Hi visheshjain
  
  The problem is not the Y-axis as I have locked it to show 0-100% always - but the width of the graph area - the X-axis. When a country, like USA, is selected as a single-selection, then the line-chart area becomes wider, as the legend takes up less space on the left. I have too many countries to have the legend in the top or bottom.