Forum Discussion

Pamiko's avatar
Pamiko
Regular Visitor
1 year ago
Solved

Deviation from average - how?

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 ch...
Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    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's avatar
Ashish_Excel
Solution Supplier
1 year ago

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.