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 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[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.
translation and formatting supported by AI

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

[danextian · Accepted solution]

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