DAX Calculate Monthly Average

0
votes

I am trying to create a measure to calculate a monthly average from a set of data that was collected every 15 minutes. I am newer to DAX and am just unsure how to intelligently filter by month without hard setting in the month ID #. The formula I am trying is:

Average Monthly Use:=AVERAGEX(VALUES('Lincoln Data'[Month]),[kWh])

Where kWh is a measure of the total usage in a column

Thanks in advance

DVDV

1
averagedaxmonthcalendar
Are you looking for the average per set of data or the average monthly total used? - Alexis Olson
This link may be useful: stackoverflow.com/questions/48709426/… - Alexis Olson
There are multiple users in the data set and each of the users has usage every 15 minutes. At the end of the day I want average monthly usage per user. - DVDV

1 Answers

4
votes

To get the monthly average usage, You need to sum up the total usage per user and divide by the total number of months for that user.

Without knowing what your tables look like, it's hard to give a very good formula, but your measure might look something like this:

= DIVIDE(SUMX(DataTable, [kWh]), DISTINCTCOUNT(DataTable[Year-Month]))