Average of Category Averages as a Measure

[rssvlk]

Can I create a Measure to average 'HydroVu Data'[value] for each 'SiteLocation'[name] with the last 24 hours and then take the average fo these averages? So the result would be a single number that describes the average, but isn't weighted in favor of SiteLocations with more data points.

So filter by DateTimeGMT, then get these averages grouped by SiteLocation[name] and then get the final average of those averages.

I looked at these and couldn't quite make sense of it:

https://community.powerbi.com/t5/Desktop/AS-AVERAGED-BY-A-CATEGORY/m-p/2057995#M768836

https://community.powerbi.com/t5/Desktop/Calculate-Average-per-category/m-p/362637#M163818

Thanks for any help!

[mahoneypat · Accepted solution]

You can use a measure expression like this.  You can use a slicer for the date range, or wrap it in a CALCULATE with a filter on date.

LocationAverage =
AVERAGEX (
    DISTINCT ( Site[LocationName] ),
    CALCULATE ( AVERAGE ( 'HydroVu Data'[value] ) )
)

Pat

[rssvlk]

Ah, Distinct was the key, perfect! Thank you!

[mh2587]

can you put some dummy data here for more details

[rssvlk]

Some data 

So averaging every row is 992.22, but what I want is an average of the averages for SensorName, which is 992.87.