Date/hour table - Create measure of day average depending on slicers

[Atiroocky]

Hello all,

My request is a bit "tricky" so i tried to simplified it the most.

My data ("Value") are stored in a date/hour database, with a value each hour.

I created a date/time table to link all my data.

I want to show a table with :

• first column = date/day/hour
• second column = maximum hour values
• third column = day average value (measure)

day_average = 
AVERAGEX( ALL(Table_date[Date_heure]), CALCULATE( [Value], VALUES (Table_date[Date] ) ))

The table will be "filtered" by slicers, especially "hour" slicer, which will reduce the size of a the days.

To do so, I already created measures, but I’m facing an issue.

The measure that calculates the "day average" values works. But it is not affected by slicers. If I want to shorten day with the slicer by 10h-19h instead of 0h-23h, the result of this measure will not change. I tried to replace  the

ALL()

by

ALLSELECTED()

whitout any change of the results.

I found a "workaround" (right table in the image) wich consists of showing a table with date/day lines (instead of date/time/hour). I add a mesure to calculate the max value of a day, and the average value. The results of these two measures are properly affected by slicers.

So, for the date/hour table, how can I write a mesure which will calculate the day average, and take care of slicers values ?

Thanks for your help.

[Atiroocky · Accepted solution]

As I continue to investigate by my side, I think I found out the issue.

Sometimes, the "Value" (which is calculated) is shown as "infiny"

When I get rid of the "infiny" value in the filter pannel, and replace ALL by ALLSELECTED in the measure, it finally works !

day_average = 
AVERAGEX( ALLSELECTED(Table_date[Date_heure]), CALCULATE( [Value], VALUES (Table_date[Date] ) ))