Dynamic Target in Gauge?

[Ojke]

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.

Thanks in advance,

Ojke.

[KGrice · Accepted solution]

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.

[Ojke]

Thanks. This solved my problem. 

Your numbers add up. 🙂