topic Re: Measure for average monthly total. AVERAGEX not quite right for this scenario in DAX Commands and Tips

Measure for average monthly total. AVERAGEX not quite right for this scenario

Ohappyday — Fri, 14 Jun 2024 16:13:29 GMT

Hi. I would like to build a measure that provides the average monthly total of volunteers, so I don't have to provide a huge total i. SUMX provides the total (see visualization), while AVERAGE X returns the monthly average per provider.

For example, if November is picked in the slicer, Volunteers active (duplicated by month) returns 359 (69+129+60+101), but AVERAGEX returns 89.75 (359/4 because there are four providers). I've tried countrows and spent a good deal of time trying various different approaches, unsuccessfully. Thank you for any help you can provide.

Here are the measures I'm currently using:

Volunteer hours received by consumers =
SUMX(
FILTER('Assessment Questions',
[Question]= "# of volunteer hours received by consumers for reporting month not reported by another agency"), [Response])

All Provider Expenses =
SUMX('Invoice Expense',
'Invoice Expense'[Invoice Amount])

Re: Measure for average monthly total. AVERAGEX not quite right for this scenario

AlexisOlson — Fri, 14 Jun 2024 18:30:28 GMT

The key is to aggregate at the level of granularity you need before you take an average.

Try writing your logic along these lines:

VAR _MonthlyHours_ = SUMMARIZE ( Dates, Dates[Month], "@TotalHours", [Volunteeer hours] ) RETURN AVERAGEX ( _MonthlyHours_, [@TotalHours] )

Re: Measure for average monthly total. AVERAGEX not quite right for this scenario

Ohappyday — Thu, 20 Jun 2024 17:15:35 GMT

Hi Alexis. Thank you for your help! I am learning DAX and may not be implementing your suggestion correctly. I tried creating a new measure using the code below but received this error: The syntax for 'RETURN' is incorrect.

VAR _MonthlyHours_ =

SUMMARIZE ( 'Calendar', 'Calendar'[Month], "@TotalHours", [Volunteer hours received by consumers] )

RETURN

AVERAGEX ( _MonthlyHours_, [@TotalHours] )

I tried creating a new table with the same code and received the same error.

Do you have any further suggestions?

Re: Measure for average monthly total. AVERAGEX not quite right for this scenario

AlexisOlson — Thu, 20 Jun 2024 21:50:17 GMT

That's not an error I'd expect with that code. Can you share a screenshot of your Power BI Desktop so I can check to see if you've missed something like naming the measure? (I didn't provide a name, so you'll definitely get some sort of error if you paste my code block in without adding one.)

Re: Measure for average monthly total. AVERAGEX not quite right for this scenario

Ohappyday — Fri, 21 Jun 2024 13:02:38 GMT

Hi. Yes, there is so much about DAX that I don't know, I probably have made an obvious error, or more than one. Is this what you are looking for?

Re: Measure for average monthly total. AVERAGEX not quite right for this scenario

Ohappyday — Fri, 21 Jun 2024 13:15:37 GMT

This is my date calendar. I wasn't sure if I should use Month or Year Month in the groupBy_columnName parameter.

Re: Measure for average monthly total. AVERAGEX not quite right for this scenario

Ohappyday — Tue, 25 Jun 2024 14:52:30 GMT

Hi Alexis. Is there anything you can suggest? Thisis one of what shoulb become about 15 such dashboards (for different programs) and I would really appreciate understanding how to create this measure.