Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ohappyday
Frequent Visitor

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

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.

Ohappyday_0-1718380560430.png

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])

 

 

6 REPLIES 6
Ohappyday
Frequent Visitor

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.  

Ohappyday
Frequent Visitor

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?

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.)

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

Ohappyday_0-1718975364119.png

 

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?

Ohappyday_0-1718974938345.png

 

AlexisOlson
Super User
Super User

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] )

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors