cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ojke
Advocate I
Advocate I

Dynamic Target in Gauge?

Hi,

 

I have a table with data concerning the sales of fruits: the type of fruit, the number of pieces sold and seller.

On my report I want a slicer on type of fruit and the seller. 

A gauge value shows the average number of pieces sold in reference to the selection on the slicers. This part is easy.

However, I want the target of the gauge to be set on the average of the number of pieces sold, only in reference to the seller slicer, not in reference to the type of fruit selected.

 

I succeed in getting the data (value and target) in two card visuals but not combined in one gauge.

 

See below for an example.

 

If all types of fruit are selected and all sellers, the target is 3.8 and the gauge value also 3.8.

If only pears are selected and all sellers, the target remains 3.8 and the gauge value is 6.

If all types of fruit are selected and only John as seller, the target becomes 2.8 and the gauge value also 2.8.

If only pears are selected and only John as seller, the target remains 2.8 and the gauge value is 2.

 

fruit_sales.png

 

Thanks in advance,

Ojke.

1 ACCEPTED SOLUTION
KGrice
Solution Sage
Solution Sage

Hi @Ojke. From your description, I think you'll need two measures defined as follows (names are up to you):

 

Average Sold = AVERAGE(TableName[number sold]) 

Average Sold All Types = CALCULATE([Average Sold], ALL(TableName[type of fruit]))

Then you'll want to use Average Sold as your gauge value, and Average Sold All Types as your target value. The only reason I'm not sure, is the math doesn't add up based on your sample data. I'm hoping you had some additional data included in your calculations but not in your pictured table. Here's what I get:

 

If all types of fruit are selected and all sellers, the target is 3.86 and the gauge value also 3.86.

If only pears are selected and all sellers, the target remains 3.86 and the gauge value is 6.

If all types of fruit are selected and only John as seller, the target becomes 3.33 and the gauge value also 3.33.

If only pears are selected and only John as seller, the target remains 3.33 and the gauge value is 2.

View solution in original post

2 REPLIES 2
KGrice
Solution Sage
Solution Sage

Hi @Ojke. From your description, I think you'll need two measures defined as follows (names are up to you):

 

Average Sold = AVERAGE(TableName[number sold]) 

Average Sold All Types = CALCULATE([Average Sold], ALL(TableName[type of fruit]))

Then you'll want to use Average Sold as your gauge value, and Average Sold All Types as your target value. The only reason I'm not sure, is the math doesn't add up based on your sample data. I'm hoping you had some additional data included in your calculations but not in your pictured table. Here's what I get:

 

If all types of fruit are selected and all sellers, the target is 3.86 and the gauge value also 3.86.

If only pears are selected and all sellers, the target remains 3.86 and the gauge value is 6.

If all types of fruit are selected and only John as seller, the target becomes 3.33 and the gauge value also 3.33.

If only pears are selected and only John as seller, the target remains 3.33 and the gauge value is 2.

View solution in original post

Thanks. This solved my problem. 

Your numbers add up. 🙂

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors