Different units of measurement in one chart visual?

[sy898661]

Hello!!

I was wondering if this was possible:

I currently have a line and stacked column chart.

The "column values" are in Grams (cumulative Actual # grams )

The "line values" are also in Grams (cumulative Planned # grams)

We wanted to add in % values (yeild variance to plan***, and target 95%) to the Line values area, but when I do that the graph doesnt display as i would hope.

***yeild variance to plan = the % of what was made compared to what we planned

I know in Excel you can choose how you want to display your data (line, column, etc) even if the line-units dont match up with the other line-units (say, one can be in grams and the other in % without messing up the graph)

This is what we are hoping for:

How can I do this in PBI (if possible)? 

Thanks so much!

[Greg_Deckler]

Well, there are quite a few options for controlling the y-axis in that chart visualization for both the line and column axis values but I am not aware of anything for controlling yet anther independent y-axis for your line axis. I'm thinking that your best bet is to somehow normalize your second set of values to your first set of values. I can't see in your chart what your values are for your y-axis line so not exactly sure it would work but I am assuming that you have values in your first set that are greater than 1 and your % are less than 1?

[sy898661]

Hey @Greg_Deckler thank you so much for the speedy reply!!! 🙂

Yess here are the numbers for my first set of data:

As for % values, for this chart they range from 0-1

(but I do have another graph that has a % value at 110%--I can worry about that later though lol!)

[v-lid-msft · Accepted solution]

Hi @sy898661 ,

With the build-in Line and stacked column chart, we cannot build a dual y-axis chart with two different y-axis (one for planned and another for percentage value), but we can multiple the percentage with the maxium planned value as a workaround, Following is the measures we used:

Actual Value = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date]) && 'Table'[Category]="Actual"))

Planned Value = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date]) && 'Table'[Category]="Planned"))

Percentage of Planned = DIVIDE(CALCULATE([Actual Value]),CALCULATE([Planned Value]),0)

Target Percent = 0.95

Percentage In Line Chart = [Percentage of Planned] * CALCULATE([Planned Value],ALLSELECTED('Table'))

Target In Line = [Target Percent] * CALCULATE([Planned Value],ALLSELECTED('Table'))

Or we can use a custom value Multiple Axes chart in the Power BI Visual marketplace which can use multi y-axis, but it does not support draw area for line chart:

Best regards,

[sy898661]

thank you so so much @v-lid-msft this is awesome! (and perfect, it is exactly what I needed!!!) 🙂