Line chart where only one item is broken down by legend

[hbirk]

Hi!

I am working on a project where I am trying to show contracts in a market compared to the rates in the given market. The market has a rate which is a continuos line. The market is made up of contracts which have a fixed rate for the contract period. I want to break down how the individual contracts are priced compared to the market rate like this:

Here the yellow line is the market rate, and the two horizontal lines are two contracts for a given contractor. To achieve this I have overlaid two graphs and made one transparet, but I would obviously like to have a single graph for it to behave correctly.

For plotting the contracts, I need to add 'ContractID' as legend field for the two contracts to not connect. If I don't, the contracts connect like this, something I don't want:

Does anyone know a way I can achieve this in the same graph? Since you can only add one field to the y-axis while using legend, I would guess I would have to create a measure for the plotting the contracts and breaking them down by legend using DAX. Can anyone help with this?

Here is a link to a pbix file with what I am trying to achieve, only in two separate graphs.

https://drive.google.com/file/d/1J8w6hwIiNDyxFI498dzni_lvdGmlWtwL/view?usp=sharing

[pi_eye · Accepted solution]

Hi @hbirk , there are couple of ways to achieve this.

The line chart can either "recognise" multiple measure expressions (multiple measures for the y axis), or one measure split up different ways .

If you have two measures, but one needs to be split up and the other doesn't, it can't tell which to make the legend and which to keep as is.

Depending on how big your dataset is, you can appeal to both ways that the line chart can be configured as thus:

1) Union the tables into one flat table with a "all" contract ID representing the market rate:

Here I have unioned the tables using DAX, but this can also be achieved using M Query:

Dax Expression:

Unioned table = UNION(  SELECTCOLUMNS( FixtureRate,"U Date",FixtureRate[Date], "U ContractID",FixtureRate[ContractID],  "U Rate", FixtureRate[FixtureRate]),
                        SELECTCOLUMNS('Rate',"U Date",'Rate'[Date]          ,   "U ContractID","Market",                   "U Rate", 'Rate'[Rate])
                        )

Resulting table:

This is then very easily turned into a line chart using the contract ID as a legend and sum (or average) rate as the Y axis:

2) One measure per contract - 3 measures in total including the market rate

This, while easier to spin up will be counter productive in the long run, as you will have to keep adding measures each time you need to add a contract. However it is very quick to do if you need something "now".

These measures are added to the Fixture rate table

Fixture Rate 1053 = CALCULATE(sum(FixtureRate[FixtureRate]),FixtureRate[ContractID]=1053)

Fixture Rate 2019 = CALCULATE(sum(FixtureRate[FixtureRate]),FixtureRate[ContractID]=2019)

Resulting chart - with market rate just "dropped" in as sum:

Hope this helps!

Pi

[hbirk]

Thank you!