Max, min, and middle for linear gauge chart values? Plus, concatenate labels

[#1]

Hi everyone!

I am making a linear gauge chart. I'm trying to make the upper limit the maximum of a few variable values. Here's what I have:

=max(sum(Quantity)/sum({<FiscalYear ={"$(=max(FiscalYear)-1)"}>}Quantity)/(sum({<[temp]={'Product 1Q1'}>}[Data])+sum({<[temp]={'Product 1 Q2'}>}[Data])))*1.1 

where the / are are section dividers. They divide them into 3 numbers, not dividing as in a mathematical formula. Hopefully I'm making sense. It makes more sense to me that they would be commas, but I'm really not sure.

So my questions are: what is the set analysis that applies selects the max value as the only value presented? I also need to find the min one and the middle one for the other boundaries in the chart. It goes red to yellow (0-min) to green (min-middle) to end (middle-max). Plus they need to have labels so I figure I would just concatenate the label in quotes like ='Plan' & min(xxxxx). Would that work?

Thanks so much for your help!

[Anonymous]

so you need median for either of these 3 quantities.

[#3]

No, I need the median of the sums for these three quantities. So the sum of quantity is 118. The sum of LY is 63. The sum of plan is 76. So I need the median of those three numbers, which is 76. Does that make sense? Thanks so much for your help!

[Anonymous]

is the dimension for all these numbers is the same because we need the dimension as well

[#5]

The dimension is by last name. It's a chart inside a chart. and the big chart's dimension is last name at least.