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.

Thanks. This solved my problem. 

Your numbers add up. 🙂

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.