AverageX, feel like I'm being an idiot...

rpiboy_1 — Mon, 14 Apr 2025 21:31:14 GMT

Not sure why I'm struggling so much with this, I just want to have an average show-up in my Matrix based on the Date Hierarchy, so average for quarter, year, etc.

Data Model:

Date (dimension) - well formed data table

Projects (dimension) - list of all projects

Months Projects Open (fact) -

each row represents a month a project is open
columns {"Project ID", "Month Open"}
has relationships to the two dimenion tables
[Month Open] is always the first of the month as that is our maximum granularity.

I wrote this measure and it works fine in a matrix with the date hierachy; as expected at the month granulirity I get the actual number of open projects, at quarter and year I get the average over the number of months (yay!):

Active Average = DIVIDE(COUNTROWS('Months Projects Open'), DISTINCTCOUNT('Date'[Year Month Number]))

When I try to use AVERAGEX with something like VALUES('Date'[Year Month Number]) it behaves like it is iterating over a single row and therefore returns the total amount of rows and not the average. I don't think I should need a Calculate as I'm not interested in changing the date context/filter, I just want to properly calculate the average over the number of months within the context. Do I need to implement a 'summarize' (seems unccessary for something otherwise so simple).

I feel like I'm missing something really obvious in terms of properly iterating over the date table?

Re: AverageX, feel like I'm being an idiot...

lbendlin — Tue, 15 Apr 2025 00:00:05 GMT

I just want to properly calculate the average over the number of months within the context.

The average of what? Your (virtual) table needs to have the values you want to average. If you want to do that across months then yes, you need to groupby or summarize or summarizecolumns over these months.

Re: AverageX, feel like I'm being an idiot...

rpiboy_1 — Tue, 15 Apr 2025 03:16:22 GMT

Duh.... My intution was correct (using the divide), but I just wasn't thinking about it properly to rationalize it in my head... Thank you.

topic Re: AverageX, feel like I'm being an idiot... in DAX Commands and Tips

AverageX, feel like I'm being an idiot...

Re: AverageX, feel like I'm being an idiot...

Re: AverageX, feel like I'm being an idiot...