topic Re: Normalize Hourly Averages by the Daily Average in DAX Commands and Tips

Normalize Hourly Averages by the Daily Average

JBGilbert — Tue, 11 Oct 2022 00:24:11 GMT

I have a list of hourly data and using the slicer to average over a date range. In the table below, I have the average hour-by-hour data shown in a table (measures). At the bottom of the table is the average of all hours over the entire date range. I would like to show the percentage (or ratio) for each hour divided by this average of all the data.

In the table below, the first row would be 5,288 / 6,385 = 0.828 Second row would be 5,226 / 6,385... Everytime I try to do it, I just end up with 1.0.

Thank you for your help

Re: Normalize Hourly Averages by the Daily Average

Shaurya — Tue, 11 Oct 2022 01:12:21 GMT

Hi JBGilbert,

Use the following DAX formula to create a new column:

Average Ratio = 'Table'[Total]/AVERAGE('Table'[Total])

Works for you? Mark this post as a solution if it does!

Re: Normalize Hourly Averages by the Daily Average

JBGilbert — Tue, 11 Oct 2022 14:21:35 GMT

Sorry, my column labels are bit unclear. The "total" column is a measure that averages the total of 2 other columns called Zone 3 and GS.FW... Total is the average flow for each hour, over a given slicer date range. However, I would like the average of all data & times, like the number 6,223 shown at the bottom of the Total column. Then I want to divide each hour by that number so I normalize on that number (the avg of all times and dates within the range). Everytime I try it, though, I just get 1.00 because averages only for that hour.

Thanks