Forum Discussion
Sales Actuals and Targets
I have one measure for Sales Actuals and three measures for Targets (namely best forecast, forecast and worst forecast). There is no time variable (year, quarter, month etc). I want to visualise these measures in one axis (i.e. chart). I tried using Bullet Chart by OKVIZ, but it does not allow more than one target, so it did not work. I also cannot create a calculated table. The expectation is that the user the can clearly see how the Actuals are tracking against each of the Forecasts.
Does anyone have a solution?
we use line charts in such scenarios. Is that an option for you?
Hi Wepenarian,
Thank you for reaching out to Microsoft Fabric Community Forum.
lbendlin Thank you for your quick response.
In this scenario, you can create a Line chart.
Line charts usually need a category axis (like time or labels), but you can create this using a disconnected table.Create a disconnected calculated table with labels as,
Mtable = DATATABLE(
"Metric", STRING,
{
{"Actuals"},
{"Best Forecast"},
{"Forecast"},
{"Worst Forecast"}
}
)
then, Create a measure to display values dynamically:SELECTION =
SWITCH(
SELECTEDVALUE(MTable[Metric]),
"Sales Actuals", SUM(chart[Sales Actuals]),
"Best Forecast", SUM(chart[Best Forecast]),
"Forecast", SUM(chart[Forecast]),
"Worst Forecast", SUM(chart[Worst Forecast])
)
Pick a Line Chart:in Axis: MTable[Metric]
in Values: SELECTION
Then, This shows all four values on a single axis, connected by a line . You can even create a clustered column chart as well in the same way.
Attached is the sample PBIX file for your reference.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
7 Replies
we use line charts in such scenarios. Is that an option for you?
lbendlin , yes...care to break down how?
Hi Wepenarian,
Thank you for reaching out to Microsoft Fabric Community Forum.
lbendlin Thank you for your quick response.
In this scenario, you can create a Line chart.
Line charts usually need a category axis (like time or labels), but you can create this using a disconnected table.Create a disconnected calculated table with labels as,
Mtable = DATATABLE(
"Metric", STRING,
{
{"Actuals"},
{"Best Forecast"},
{"Forecast"},
{"Worst Forecast"}
}
)
then, Create a measure to display values dynamically:SELECTION =
SWITCH(
SELECTEDVALUE(MTable[Metric]),
"Sales Actuals", SUM(chart[Sales Actuals]),
"Best Forecast", SUM(chart[Best Forecast]),
"Forecast", SUM(chart[Forecast]),
"Worst Forecast", SUM(chart[Worst Forecast])
)
Pick a Line Chart:in Axis: MTable[Metric]
in Values: SELECTION
Then, This shows all four values on a single axis, connected by a line . You can even create a clustered column chart as well in the same way.
Attached is the sample PBIX file for your reference.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara ReddyHi Anonymous , the report I am working on is connected to a Semantic model which does not allow me to add calculated tables.
Hi Wepenarian ,
we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
Hi Wepenarian ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
Hi Wepenarian ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
