Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello,
I am trying to create a Guage visual that has the Revenue to date as Actuals, Budget to date as Target and Total budget for the full year as the Maximum value. I require the actuals/budget to date to be dynamic based on a date slicer I have, and all 3 variables to be dynamic based on a Sector slicer I have.
Is there a way to get this to work as I am struggling with getting the maximum value to stay as the full year budget (but change based on Sector picked)? Currently I either get it where it is also dynamic with the date slicer (so only shows YTD), or it shows the full year for all Sectors as a whole, even if only one selected in the slicer.
Thank you
Solved! Go to Solution.
Hi @AaronO23 - Create a measures will be filtered based on the selected date range from the date slicer (Actual to Date and Budge to Date measures as follows:
Actuals_ToDate =
CALCULATE(
SUM('YourTable'[Revenue]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)
Budget_ToDate =
CALCULATE(
SUM('YourTable'[Budget]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)
To ignore the date slicer but still respect the sector slicer create another measure as below for total year budget
Total_Year_Budget =
CALCULATE(
SUM('YourTable'[Budget]),
ALL('DateTable')
)
using above measure and also placing the created fields in actuals, targets and maximum value with Total year budget. you will see the dynamic Gauge visual in Power BI that adjusts based on both date and sector slicers
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Proud to be a Super User! | |
Hi @AaronO23 - Create a measures will be filtered based on the selected date range from the date slicer (Actual to Date and Budge to Date measures as follows:
Actuals_ToDate =
CALCULATE(
SUM('YourTable'[Revenue]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)
Budget_ToDate =
CALCULATE(
SUM('YourTable'[Budget]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)
To ignore the date slicer but still respect the sector slicer create another measure as below for total year budget
Total_Year_Budget =
CALCULATE(
SUM('YourTable'[Budget]),
ALL('DateTable')
)
using above measure and also placing the created fields in actuals, targets and maximum value with Total year budget. you will see the dynamic Gauge visual in Power BI that adjusts based on both date and sector slicers
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Proud to be a Super User! | |
This is great, thank you. As a separate problem but on the same visual, do you know how to fix the issue with negative values. I have some sectors that have both a negative actual and budget (as they are pure cost centres), and so when I filter to these sectors, the guage visual won't actually show any bar fill at all (see below visual).
Hi @AaronO23 - create below measures on actualto date, budget to date, and fullyearbudget with ABS function.
PositiveActualsToDate = ABS([ActualsToDate])
PositiveBudgetToDate = ABS([BudgetToDate])
PositiveFullYearBudget = ABS([FullYearBudget])
use the above measures in your guage chart in below fields.
Actual Value: PositiveActualsToDate
Target Value: PositiveBudgetToDate
Maximum Value: PositiveFullYearBudget
Try it and let know.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Proud to be a Super User! | |
User | Count |
---|---|
84 | |
79 | |
71 | |
48 | |
43 |
User | Count |
---|---|
111 | |
54 | |
50 | |
40 | |
40 |