I’m working on a visual which is a number of guages, each related to a particular location within the organisation. I’m trying to create a measure which will give me the same max value across all locations. As the numbers can vary quite a lot I need this to be dynamic, rather than a set amount. I’ve defined this as the largest sum of values across the given time span, across all shops.
When testing the query it’s doing the calculations perfectly, I get a table of the summarized values for each location, but I can’t seem to figure out how to return the max value across all. Depending on where I try to remove any filters, I either get the sum total of all locations put together, or only the sum total for that location alone.

This is my current query:

`TargetGuageMax =
var __hourtbl = CALCULATETABLE(
SUMMARIZE(
Data
, Data[Shop]
, “MaxHours”, CALCULATE(SUMX(Data,[Hours])
)
)
, ALLEXCEPT(Data,Data[PeriodEnd])
, FILTER(Data,Data[Shop]<>”LocationXX”)
, FILTER(Data,Data[Shop]<>””)
, FILTER(Data,Data[Shop]<>”LocationYY”)
)

var guagemax = CALCULATE(MAXX(__hourtbl,[MaxHours]))
return guagemax`

The last var is only there so I could try a few different solutions easily by changing the return var.

This is how the page currently looks. The table is calculating correctly, and the value in the card is what I’m looking for. I just need that to be the max value of all the guages. Any help would be appreciated.

Example of guages