How to display a diagonal reference line (Y=X) in line chart ?

[Baelya]

Hi all,

I'm working on a line chart where I have set my X-axis, Y-axis, a legend (to have multiple "series") and some tooltips to provide more data information 

On top of that, I also need to add a sort of diagonal reference line Y=X. It would greatly help interpreting the results

Here is how I would like to display things

The objective is then to focus on one of these "legend" lines and compare it with the black diagonal line Y=X (that I've add manually in paint here)

I tried another approach using a Line and Clustered Column Chart, but it doesn't meet my needs. I'm unable to display additional information or tooltips for the line, and I also can't show multiple lines based on the legend (Legend and tooltips are only related to the columns)

Here, the Y=X can be assimilated to the column bar and i am now able to see when my line is below or under the diagonal. But as said, I cannot display a legend for my line so I can get mutilple lines for each series as before. 

Any help ?
Thanks 

[Baelya · Accepted solution]

Hi @v-hjannapu 

Thanks for your support.

The problem with this method is that I lose all filtering capabilities from my original working table.

I thought of creating a version of the YXLine table that includes the key columns used in my main table (i.e., a concatenation of columns that form a unique key in my original table).

This way, I could establish a relationship between the CombinedTable and my working table, which would allow me to filter the new visual using all the relevant fields.

However, this feels overly complex…

As a workaround — and maybe a simpler one — I created a measure that calculates SL % - SL LEVEL 11 %, so the reference line becomes just the value 0. It's quite easy to visualize.

For more clarity, I'm hoping there's a way to just draw a horizontal line at Y = 0 ?? 
Please tell me there's an easy way to do that! 😅

[Baelya]

I've tried looking into this option however how/where to add this sample Y=X serie in my visual ? 

I cannot add two values in the Y-axis field nor in "Secondary Y-axis"

[v-hjannapu]

Hi @Baelya,

By looking to add a diagonal reference line (Y = X) to make comparisons easier in your chart. While Power BI doesn’t have a built-in option for this, there’s a simple workaround you can use to get the same result.
 

To create the diagonal line, you can use the following DAX expression.

GeoName = "Y = X"

After adding this column to your table, include the GeoName field in the Legend section of your visual.

 find the attached Screenshot for your reference:

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Harshitha.
Community Support Team

[v-hjannapu]

Hi @Baelya,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.

Harshitha.

Community Support Team.

[Baelya]

Hi @v-hjannapu ,

Unfortunately, as shown in my first screenshot, my visual already contains a legend. 

It seems I cannot add a second one to display the Y=X

Thanks

Thanks.

[v-hjannapu]

Hi @Baelya,

To get around this, you can add the Y = X reference line to your dataset by appending it to your main data table and giving it a value in the same column you're using for the legend (e.g., GeoName = "Y = X"). This way, the line shows up in the same legend with your existing series.

Create a small reference table with SL LVL11 % = SL % for a few matching values .

Add a column like GeoName = "Y = X" to match your legend format.
A sample DAX formula to create the Y=X reference table

YXLine =

VAR MaxValue = MAX('Table'[SL LVL11 %])

VAR MinValue = MIN('Table'[SL LVL11 %])

RETURN

GENERATESERIES(MinValue, MaxValue, 1)

Append this reference table to your main dataset with Power Query or DAX.

Append this new YXLine table to your existing dataset

CombinedTable = 
UNION(
    SELECTCOLUMNS('YourTable', "SL LVL11 %", [SL LVL11 %], "SL %", [SL %], "GeoName", [GeoName]),
    'YXLine'
)

Use GeoName as the Legend field ,now your diagonal line will show up with your other series and be distinguishable in the legend.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Harshitha.
Community Support Team

[Baelya · Accepted solution]

Hi @v-hjannapu 

Thanks for your support.

The problem with this method is that I lose all filtering capabilities from my original working table.

I thought of creating a version of the YXLine table that includes the key columns used in my main table (i.e., a concatenation of columns that form a unique key in my original table).

This way, I could establish a relationship between the CombinedTable and my working table, which would allow me to filter the new visual using all the relevant fields.

However, this feels overly complex…

As a workaround — and maybe a simpler one — I created a measure that calculates SL % - SL LEVEL 11 %, so the reference line becomes just the value 0. It's quite easy to visualize.

For more clarity, I'm hoping there's a way to just draw a horizontal line at Y = 0 ?? 
Please tell me there's an easy way to do that! 😅

[Baelya · Accepted solution]

Edit : 

think I've found it 

[v-hjannapu]

Hi @Baelya,

We really appreciate your efforts and for letting us know the update on the issue.

Please continue using fabric community forum for your further assistance.
please mark as Accept as solution so that other community members can find it easily.

[v-hjannapu]

Hi @Baelya,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Best Regards,
Harshitha.
Community Support Team

[v-hjannapu]

Hi @Baelya,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Best Regards,
Harshitha.
Community Support Team

[GilbertHall]

Power BI doesn’t have a built-in diagonal reference line option in line charts. A common workaround is to create a calculated series with values equal on X and Y axes, then add it as a line to your chart. This simulates the diagonal line you need.