Gauge Problems - Defining Min/Max Values based on Grouping

[iFixRobots]

Hi Everyone,

I have the following problem: 

I'm trying to create a Gauge that can change based on CreatedDate Slicer. The number of this gauge should reflect always # of Cases. In addition to this, there's a 3 level slicer filters in order to drill down the following: 

Case Created Date:

--

Queue

Manager

Support Agent

Min, Max, and Value should come from the following equation:

Min = Agent with least amount of Cases

Max = Agent with largest amount of Cases

Value = Agent selected using drop down

Now, what happens if there's no drop down select? Then it should calculate Min, Max and Value based on those high level filters.

Essentially I'm trying to calculate the weight of an individual based on their efforts compared to the group. The group is associated to the Manager Name. ( I hope I'm making sense at this point)

I've been trying to do this but it seems like this is quite the complicated formula... Any help is appreciated.

PS: I would also love to do this with Resolution Rate, since it should essentially be the same formula, just changing variable filtering. 

Thank you! 

[v-chuncz-msft]

@iFixRobots,

You may take a look at this discussion and use HASONEVALUE Function in nested IF Function to detect the drill down level.