0

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

DVDV
  • 15
  • 1
  • 1
  • 4
  • Are you looking for the average per set of data or the average monthly total used? – Alexis Olson Feb 14 '18 at 21:01
  • This link may be useful: https://stackoverflow.com/questions/48709426/average-gives-incorrect-number-power-bi-desktop/ – Alexis Olson Feb 14 '18 at 21:03
  • 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 Feb 14 '18 at 21:04

1 Answers1

4

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]))
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thanks for the quick response Alexis! To confirm, is this code for a measure? Also, I am using DAX in Power Pivot not Power BI – DVDV Feb 14 '18 at 21:45
  • @DVDV Yes, it's intended to be a measure. The DAX should be the same unless you've got an old version and are trying to use newer functions. – Alexis Olson Feb 14 '18 at 21:48