Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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,
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?
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!)
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,
thank you so so much @v-lid-msft this is awesome! (and perfect, it is exactly what I needed!!!) 🙂
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |