Forum Discussion

JBGilbert's avatar
JBGilbert
New Member
3 years ago

Normalize Hourly Averages by the Daily Average

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

 

 

 

2 Replies

  • Shaurya's avatar
    Shaurya
    Icon for Memorable Member rankMemorable Member
    3 years ago

    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!

  • JBGilbert's avatar
    JBGilbert
    New Member
    3 years ago

    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