Multi-level aggregation using DAX measures

cnesr · ‎07-18-2018

Hello,

I am very new to Power BI Desktop. Thank you very much for your help.

I have a table of traffic data with the following columns:

Date & Time (by hour)
Date (by day)
Year (2012 to 2018)
Month (1 to 12)
Day (1 to 31)
Day of Week (Monday to Sunday)
Hour (0 to 23)
Northbound Vehicle Counts
Southbound Vehicle Counts
Total Vehicle Counts

I need to calculate the Monthly Average Daily Traffic (MADT). MADT is calculated by averaging the Hour of the day by the Day of Week by the Month of the Year. This will yield a 24-hour profile for each Day of Week (Monday to Sunday) by Month and Year. The 24-hour profile is then summed to a produce a daily traffic count. These 7 daily traffic figures are then averaged to create the MADT.

I think this is a matter of using nested AVERAGEX functions to create a measure, but I have not had success. I have had success in Excel, but I would really like to do it in Power BI.

This is probably terribly explained. Please let me know what further details are required.

Thank you so much for your help!!

Seward12533 · ‎07-18-2018

You may want to look at Date-Time Table with granularity for every hour of the day for every day from 2012-2018 then relate that to yoru Date and Time by hour column. You then just need to write a Simple Measures for the averages. Total Average = AVERAGE(table[total vehicle counts])

You can then use the Time Inteligence features of DAX. Tip youse the Day, Hour, Week, Month, Year from the date-time table not your Data Table and to keep things clean I'd remove all your Date and time related columns other than your Date/Time column.

See this link for creating the DateTime Table in Powerquery https://community.powerbi.com/t5/Desktop/Has-anyone-created-datetime-table-using-QueryEditor-or-DAX...