Dynamic Gauge using Slicers

[Anonymous]

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

[Anonymous]

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.

[v-juanli-msft · Accepted solution]

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

[v-juanli-msft]

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.
“

[Anonymous]

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

[v-juanli-msft]

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

[Anonymous]

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.