Add a Secondary Y axis on a Line Chart with Legend

[GE1]

This has been asked before. But it still doesn't appear to be possible. Is it possible? Can we create line graphs where one axis has a legend and the other has a line or lines unrelated to that legend (or, let's be radical, it's own legend)? It's possible in Excel, which is awful for graphs...

There are so many situations where you want to compare patterns over time of different types of data. That is the point of Power BI. That's how you get insight. Often the only thing these datasets have in common is the date, which is on the graph, so there is something to plot against. The point of the secondary axis is obviously that two datasets with different units can be put on the same graph. What is the point of a secondary axis otherwise? Why can't we add legends?

[Ritaf1983]

Hi @GE1 

Most of us are not able to compare trends in lines that intersect, because we need to remember which line belongs to which category. This is because our short-term memory is limited, and it takes a lot of energy to decode complex information.

I recommend reading Edward Tufte's work, which explains these points in detail.

As analysts, we often forget that our goal is to communicate our messages to users in a way that is easy, quick, and accurate. The graph in the attached image is far from that.

However, I also don't understand the technical problem.
Power bi allows creating a graph with 4 lines and 2 axes...
What do I miss in your question?

[GE1]

Ok, thanks for the advice. I think, though, you are forgetting that not all audiences are the same. Some are very technical and know their data so well they can deal with a lot more than others. They ask for it, in fact.

But, in any case, I cannot replicate what you've shown. If I create a line graph with a legend based on another attribute (i.e. not simply the name of the datapoint) and try to add a second axis, it will not let me add the data point to the second axis.

So if dataset 1 is sales volume by month, I can add dataset 2 (temperature) to the second axis.
But if dataset 1 is sales volume by month, and the legend is 'type of fruit' (giving me two lines), I cannot add dataset 2 (temperature) to the second axis. It simply doesn't accept it.

I think, from what you've done, the only way to do this is to create two completely separate datasets for sales volume - apples volume and oranges volume - and add those both as separate lines to the first axis. For some reason, this is ok with Power BI. But then total sales has to be calculated separately.

[Ritaf1983]

The discussion about data visualization and the brain and the fact that our visual perception is the same, whether we are geniuses or 6-year-old children, is too long and too deep for the scope of this forum discussion, and English is not my native language, so it is less suitable for development now. My recommendation is based on years of experience and deep research, including editing a book on the subject, and it is up to you to take it or not.

Regarding the technical issue, different measures are indeed required. In a case like the image, you need 4, just like in the file I attached to you. That's how the tool works.

Have a nice evening and good luck, Rita.

[GE1]

Sorry, have you just said what I want to achieve is "One of the worst types of visuals from an effective data visualization perspective"? Is that the excuse for it not being possible?

By the way, you are wrong. Obviously, any graph can be misleading - but Power BI still allows people to start axes at different points, for example, which is actually the main criticism levelled in the article you linked to, not the fact that it is dual axis, which it says is very useful!

The video you linked to (which isn't an answer to this) creates a much more confusing visual than my apples and oranges example. If I want to show the sales of apples and oranges against temperature, I can't. That's only 3 lines and would be very, very clear.

[GE1]

[Ritaf1983]

Hi @GE1 
There are 4 measures with 2 y axes by their types. 
One of the worst types of visuals from an effective data visualization perspective, but technically it is possible to create them.
The reasons why not are here :
https://www.datarevelations.com/be-careful-with-dual-axis-charts/

If you still want to create the tutorial:

https://www.youtube.com/watch?v=gkMjAYFeSiM

or just share data in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

and I will show you how it works...again not recommended...

   If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @GE1 
Can you please show the desired result?