Creating an average

[sbott]

Hi, I have a report page that has a number of charts. One of the charts displays the months along the x-axis, with the corresponding number of hours associated with each month represented by a bar. The chart is filtered by a couple of slicers, one is the customer, and the other is the date range.

What I want is to provide the monthly average of hours, dependent on the number of months selected.

I have very little experience with DAX, and I assume that I will need to create a new measure. I have made a few attempts using AVERAGEX, but TBH, getting nowhere.

Could anyone be of assistance?

[V-yubandi-msft]

Just checking, @sbott , did you get a chance to review my response? If it meets your requirements and expectations, kindly mark it as the accepted solution so it can assist other community members facing similar issues. 

Thank You.

[V-yubandi-msft]

Hi @sbott ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank You.

[sbott]

Apologies, I tried it last week and it didn't come out right, but I have been busy this week so not yet had time to try again.

[V-yubandi-msft]

Hello @sbott ,

Thank you for your response, sbott. If possible, please try it and let us know the result. For any future questions or discussions, we encourage you to continue engaging with the community.

— Yugandhar
Community Support Team.

[V-yubandi-msft · Accepted solution]

Hi @sbott ,

Thanks for posting your query in the Microsoft Fabric Community. I would like to suggest another tested approach that works effectively even without a separate date table, especially if you already have a MonthYear field in your main table.

Monthly Average Hours = 
VAR TotalHours = SUM('Table'[Sum of Hours])
VAR VisibleMonths = DISTINCTCOUNT('Table'[MonthYear])
RETURN DIVIDE(TotalHours, VisibleMonths)

FYI:

Thanks for your response @sbott .

I've attached a PBIX file for more details. Please review it and let me know your insights.

If my response solved your query, please mark it as the Accepted solution to help others find it easily.

And if my answer was helpful, I'd really appreciate a 'Kudos'.

[burakkaragoz]

Hi @sbott ,

You’re on the right track with AVERAGEX. To calculate the average number of hours per month based on the selected date range and filters, you can try something like this:

Monthly Avg Hours = 
AVERAGEX(
    VALUES('Date'[Month]),
    CALCULATE(SUM('YourTable'[Hours]))
)

Make sure 'Date'[Month] comes from your date table and that it's properly related to your data table. This measure will dynamically average the total hours per month, based on whatever filters (like customer or date range) are applied.

Let me know if your model is structured differently and I can help adjust it.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI