Forum Discussion
Dynamic Gauge using Slicers
Please excuse me if this topic has been covered before but the posts that I found did not help me towards my goal. I am fairly new to Power BI and I am trying to set up a gauge for a budget spreadsheet that dynamically changes with slicers.
My current slicers are:
Account Name
Date ( Year)
I have a set number for each account every year and I am unsure what the best way is to format the data so that when I select slicers it adjusts the maximum on the gauge to match a budget that i set per account, per year/maybe even per quarter.
Thank you,
Michael
Hi Anonymous
Please look into my test, is this what you want?
dataset:
create measures:
min_value = 0 max_value = CALCULATE(SUM(Sheet2[values]),ALL(Sheet2))
171 is the total values of the table.
If you have any problem, please let me know.
Best Regards
Maggie
6 Replies
Hi Anonymous
As tested with this dataset,
date account value max
1/1/2016 a 1 2 1/1/2016 b 2 3 1/1/2017 a 3 4 1/1/2017 b 4 5 when i add "date" and "account" in differnt slicer, selecting items from slicer could reflect the gauge.
Best Regards
Maggie
Hi Anonymous
since you have data in teo different tables, if it doesn't support creating any relationship between them,
and they have the same columns, for example:
table1:
account | date | value | budget
table2:
account | date | value | budget
Please open Editor Queries, append two tables.
Best Regards
Maggie“
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
“I created a sample data set that shows the amount of layers involved. In the default screen it would be nice if the gauge didnt show anything, and it only activates when you select slicers. I am not sure how to accomplish my goal of having the Gauge change based on budget. I am unfamiliar with how to use relationships. Please excuse my noobiness. In the actual dataset there are around 20,000 lines of data
ffbibire
This Image kinda gives a bit of a breakdown. These two Account types on the top are in the same excel sheet but different sheets on PBI Desktop. Beneath that they each have their own account budgets. Beneath that is budget by year. There are also budgets by quarter but I am not going to go that deep right now.
Here is the template for the Report I am creating. I have the budgets on a separate sheet in a completely different format than what can be used. I Suppose my question turns into how may I format this. There is a visual level filter on each budget group. I just need to make the budget a target OR the max value on the gauge. I hope all of this makes sense and you can help me. P.S don't mind the color scheme, that is a work in progress.
Hi Anonymous
Please look into my test, is this what you want?
dataset:
create measures:
min_value = 0 max_value = CALCULATE(SUM(Sheet2[values]),ALL(Sheet2))
171 is the total values of the table.
If you have any problem, please let me know.
Best Regards
Maggie