CODEWITHSUNDEEP
powerbi
donquijote
donquijote

Reputation: 1812

PowerBi line chart adding lines based on measures

With Power BI line charts there is a limitation of not being able to add more than one "Values" field when a "Legend" field has been defined.

The feature I need is to simply be able to plot a horizontal line based on a measure I calculate via DAX.

Currently the Analytics tab allows to add constant lines based on max/min/avg of Value but doesn't allow to specify a measure from the data model to plot as horizontal line.

Can someone provide a solution or work around to be able to plot additional horizontal lines on line chart?

Upvotes: 0

Views: 7621

Answers (1)

Leonard
Leonard

Reputation: 2578

The reason you can't put a second measure on your chart when you have a legend defined is because the legend creates multiple colored lines out of a single measure. If there were 2 measures, it would expect to convert both measures into multiple colored lines too, and then you'd have 2 lines that correspond to each legend item. Either the chart would have two lines with the same color, or you'd need 2 colors for the same legend item. Neither of which are very clear.

I don't think that's what you're looking to accomplish with a second measure, though. You're looking to add a reference line on the chart based on the second measure, not split the 2nd measure out by the legend. It's a reasonable request.

If your legend isn't highly dynamic and doesn't have too many items, you can create a measure for each legend item.

Say you have a Sales measure and a Sales Category with 5 items (Cat1-Cat5 for simplicity). Create 5 measures, each filtered to one legend item. 

Cat1 Sales:=CALCULATE([Sales],'Your Table Name'[Sales Category]="Cat1")
Cat2 Sales:=CALCULATE([Sales],'Your Table Name'[Sales Category]="Cat2")
....

You can then remove Sales Category from your legend, and remove Sales from your Values. Instead, in Values, place the 5 measures: Cat1 Sales - Cat5 Sales. This will make one line per legend item, so your chart shouldn't look too different. However, now you've done that, you can also drag on a 6th measure as your reference line. It's not the greatest solution but it should work as a stop-gap.

In the format section of your line chart, you can also change the data color for each measure e.g. if you want your reference line black and your measures shades of blue.

There are several ideas in the Power BI ideas forum requesting a reference line based on a measure, and I do recommend adding your voice to them too. E.g. https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15497754-ability-to-dynamically-with-dax-functions-or-meas or https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13296177-dynamic-reference-lines

Upvotes: 3

Related Questions