Deviation from average - how?

[Pamiko]

Hi All,

I have this table about monthly sales value, however in case I draw an average line, the differences are really small, so I would like to show deviation from average in a different bar chart. I have already calculated the average monthly sales, which is annual sales/12. In the next step I have stucked. I need to calculate monthtly sales minus average sales and then put those into bar chart. How would you do that? I cannot add new column, because I have daily order data.

ChatGPT just gave this measure, but that does not work:

Monthlysales =
CALCULATE(
SUM('Pizza orders'[Ordervalue]),
ALLEXCEPT('Pizza orders', 'Pizza orders'[Month])
)

Thank you in advance for your help.

[v-venuppu]

Hi @Pamiko ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @burakkaragoz @danextian @Ashish_Excel for the prompt response.

I want to check if you had the opportunity to review the information provided and resolve the issue.If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[burakkaragoz]

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.

[Pamiko]

Thank you for the detailed answer. Unfortunately I still have issues. Attaching picture:

Év = Year

Hónap = Month (sorry, I am working on my mother language as well).

My measures:

Monthly Average =

  AVERAGEX(

    ALL('Naptár'[Hónap]),

    CALCULATE([Totalsales])

  )

Totalsales = SUM('Pizza orders'[Rendelesertek])

Diff =

[Totalsales] - [Monthly Average]

Total sales value is: 817860 k, what I can see in the chart.

I do not understand, what can be the issue, but I would really appreciate your help.

[burakkaragoz]

Hi @Pamiko ,

Thanks for sharing more details and your screenshot. Let’s clarify what’s happening and how you can fix it.

1. Why your Monthly Average isn’t working as expected:
Your current formula:

DAX

Monthly Average =
AVERAGEX(
    ALL('Naptár'[Hónap]),
    CALCULATE([Totalsales])
)

This is calculating the average over all months, but if your [Totalsales] measure does not adjust for filter context correctly, it can either return the same value for every month, or sometimes the total value for all months.

2. The correct approach:
You want to get the average monthly sales (so, "total sales for the visible months" divided by "number of visible months"), and then for each month, subtract the average from that month's total.

Here’s a robust way to do it:

• Total Sales:

  Totalsales = SUM('Pizza orders'[Rendelesertek])

• Monthly Average (per visible months):

  Monthly Average =
      CALCULATE(
          [Totalsales],
          ALLSELECTED('Naptár'[Hónap])
      ) / CALCULATE(
          DISTINCTCOUNT('Naptár'[Hónap]),
          ALLSELECTED('Naptár'[Hónap])
      )

  • This takes the total sales for all visible months and divides by how many months are visible in your chart, which is usually what you want for a "bar chart deviation from average".

• Deviation:

  Diff = [Totalsales] - [Monthly Average]

3. What to check:

• Make sure your visual’s axis is using 'Naptár'[Hónap] from your calendar/dim table, not from your orders table.
• If you are slicing by year, make sure your DAX respects the year as well (so average is not for all years combined unless that’s what you want).
• Use ALLSELECTED instead of ALL to make sure slicers and filters are respected in the average calculation.

4. Important:

• If you want the average for each year, add [Év] to both numerator and denominator in the DAX above.
• If you want a global average (all months, all years), just use ALL('Naptár'[Hónap]) instead.

5. Practical test:
Try putting this new [Monthly Average] measure in a card visual. It should show the same number you’d get if you summed all visible months’ sales and divided by number of visible months. Each month’s [Diff] should tell you how much above or below average it is.

Let me know if this fixes it or if you see any odd numbers—happy to check your sample PBIX if you can share a dummy version!

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.

[burakkaragoz]

Hi @Pamiko ,

Thanks for sharing more details and your screenshot. Let’s clarify what’s happening and how you can fix it.

1. Why your Monthly Average isn’t working as expected:
Your current formula:

DAX

Monthly Average =
AVERAGEX(
    ALL('Naptár'[Hónap]),
    CALCULATE([Totalsales])
)

This is calculating the average over all months, but if your [Totalsales] measure does not adjust for filter context correctly, it can either return the same value for every month, or sometimes the total value for all months.

2. The correct approach:
You want to get the average monthly sales (so, "total sales for the visible months" divided by "number of visible months"), and then for each month, subtract the average from that month's total.

Here’s a robust way to do it:

• Total Sales:

  Totalsales = SUM('Pizza orders'[Rendelesertek])

• Monthly Average (per visible months):

  Monthly Average =
      CALCULATE(
          [Totalsales],
          ALLSELECTED('Naptár'[Hónap])
      ) / CALCULATE(
          DISTINCTCOUNT('Naptár'[Hónap]),
          ALLSELECTED('Naptár'[Hónap])
      )

  • This takes the total sales for all visible months and divides by how many months are visible in your chart, which is usually what you want for a "bar chart deviation from average".

• Deviation:

  Diff = [Totalsales] - [Monthly Average]

3. What to check:

• Make sure your visual’s axis is using 'Naptár'[Hónap] from your calendar/dim table, not from your orders table.
• If you are slicing by year, make sure your DAX respects the year as well (so average is not for all years combined unless that’s what you want).
• Use ALLSELECTED instead of ALL to make sure slicers and filters are respected in the average calculation.

4. Important:

• If you want the average for each year, add [Év] to both numerator and denominator in the DAX above.
• If you want a global average (all months, all years), just use ALL('Naptár'[Hónap]) instead.

5. Practical test:
Try putting this new [Monthly Average] measure in a card visual. It should show the same number you’d get if you summed all visible months’ sales and divided by number of visible months. Each month’s [Diff] should tell you how much above or below average it is.

Let me know if this fixes it or if you see any odd numbers—happy to check your sample PBIX if you can share a dummy version!

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.

[danextian]

@burakkaragoz 

Another AI copy-paste without validation?

@Pamiko 

Since Total is a measure, it doesn't need to be wrapped in CALCULATE - it is implicitly applied.

This measure will not return the overall average for each month but simply the current month's value. It will at the total level.

Monthly Average = 
AVERAGEX ( ALL ( Dates[Month Short] ), [Total Revenue] )

 If you want the average to be applied to each month, modify the filter context by using CALCULATE

Monthly Average CALCULATE = 
CALCULATE (
    AVERAGEX ( VALUES ( Dates[Month Short] ), [Total Revenue] ),
    ALLSELECTED ( Dates ) --applies to all visible rows only, use ALL to apply to the whole Dates
)

[Ashish_Excel]

Hi,

Ensure that you create a Calendar table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of yoru Data table to the Date column of the Claendar table.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures

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

Monthly Average = averagex(allexcept(calendar,calendar[year]),[Total])

Diff = [total]-[monthly average]

If this does not work, then share the download link of the PBI file.