Calcualting Max hourly value

Question

&nbsp;I am looking to create a measure to calculate the max hourly value, for each hour over a 24 hour period, across several days.&nbsp;&nbsp;This is summing up the hourly values instead of giving me the max.&nbsp;Max Hourly Value = 
MAXX (
	SUMMARIZE (
		dimtime,
		dimtime[StartOfHour]
	),
	SUM('table2'[value])
)Table2 is the main fact table with a datetime column which is related to the dimtable .&nbsp;

phil_seamark · Answer

Hi&nbsp;juju&nbsp;Can you please give some sample data. &nbsp;How does your Table2 relate to your dimTime table?

juju · Answer

&nbsp;Hello&nbsp;Phil_Seamark&nbsp;Here's a link to sample data pbix file.&nbsp; Thxx&nbsp;sample data&nbsp;&nbsp;

phil_seamark · Answer

Hi&nbsp;juju&nbsp;I'm not sure you need an hour. &nbsp;I just draged your 'StartOfHour' column to a table (formatted the time) and then just dragged your 'Value' field as well, but used the menu to use the MAX calucation to show the best value&nbsp;&nbsp;While this is a simple approach, I suspect you'll run into issues when you expand this to your real data.&nbsp;Have you considered a calculated table?&nbsp;Table = SUMMARIZE(
                'table2',
                dimtime[StartOfHour] ,
                "Max Value" , MAX('table2'[value]) ,
                "Min Value" , MIN('table2'[value]) ,
                "Median Value" , MEDIAN('table2'[value]) -- ETC ETC
                )&nbsp;

juju · Answer

Phil_Seamark&nbsp;The approach above doesnt seem to be working for me. &nbsp;My data is on a minute by minute level, so the above approach is rather giving me the max value at the minute level for each hour. &nbsp; Rather, I am looking for the max value of the total for each hour , across several days - i.e aggregate the minute data, then find the max value for each hour over several days. Hope this helps.

anonymous · Answer

juju,
Create a new table using DAX below, then create a rank column in the new table and set its values to 1 in the table visual.
&nbsp;
Table = SUMMARIZE(
                'table2',
                dimtime[StartOfHour] ,
                "Sum Value" , Sum('table2'[value]) 
                
&nbsp;
RankColumn = RANKX(ALL('Table'),'Table'[Sum Value],,DESC)

If the above DAX don’t help, please post your expected result in table based on the sample data in the PBIX file.
Regards,

Forum Discussion

Calcualting Max hourly value

5 Replies

Related content

Hourly Refresh?

Calcualted column

Hourly average sales

Chart for Hourly Data

Storing hourly data