Forum Discussion

sbott's avatar
sbott
Helper I
1 year ago
Solved

Creating an average

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. T...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    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's avatar
burakkaragoz
Super User
1 year ago

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