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

Atiroocky — Tue, 11 Jun 2024 07:36:27 GMT

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()

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.

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

Atiroocky — Tue, 11 Jun 2024 08:08:43 GMT

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] ) ))

topic Date/hour table - Create measure of day average depending on slicers in DAX Commands and Tips

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

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