Deviation from average - how?

Hey Pamiko ,

I checked your message and also reviewed Ashish_Excel 's reply in the forum. You’re very close, but I get why it’s confusing, especially when working with measures and not calculated columns.

Here’s how I’d approach it (I’ve dealt with similar pain in Power BI before):

1. Calendar Table is a must: Make sure you have a calendar/date table in your model, with at least columns for [Date], [Year], [Month Name] and [Month Number]. If you don’t have one, you can generate it in Power Query or with DAX.

2. Relationships: Your 'Pizza orders' table’s date column should be related to the [Date] column in your calendar table (single direction is fine).

3. Measures:

   • Monthly Sales (Total):
     DAX
      

     Total = SUM('Pizza orders'[Ordervalue])

   • Monthly Average:
     This one’s important—use AVERAGEX over all months in your calendar, not just a “plain average.”
     DAX
      

     Monthly Average = 
       AVERAGEX(
         ALL('Calendar'[Month]), 
         CALCULATE([Total])
       )

   • Deviation from Average (Diff):
     DAX
      

     Diff = [Total] - [Monthly Average]

4. Visual: In your bar chart, use [Month Name] from the Calendar table on the axis, and show the [Diff] measure as the value. That will let you see how much each month’s sales are above or below the average.

Extra tips:

• If the months are not sorted correctly, sort [Month Name] by [Month Number] in your calendar table.
• Make sure you’re using measures, not columns, for these calculations.
• If you still get the same values for every month (sometimes happens with filter context), double-check the ALL or ALLSELECTED part in the Monthly Average measure.

Let me know if you hit any blockers, I can send more detailed screenshots or even a sample PBIX if needed.

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