calculating the summation of differences between sales value and the sales average

[mahfav]

Hi everyone,

I have a question regarding my powerbi dashboard. I need a measure to calculate the summation of differences between sales values (for each month - 1 to 6) and the average of sales. Accordingly, I have written a formula for calculating the sales average as the follows (it works correctly):

Avrsales = averagex(values(table[month]),table[sales])

However, for calculating the Mean average differences (MAD), this formula returns zero, which is a wrong number (it must return 84 for the sample data in the attached photo

MAD = sumx(values(table[month]),abs(table[sales]-Avrsales))

Please see the attached, which is the sales figure for each month (first six months of the year).

I will be so grateful if you may help with your suggestions/solutions.

Thanks in advance

[parry2k]

@mahfav I think you need this but hard to tell without the data:

MAD = 
SUMX (
   VALUES ( Table[month] ),
   ABS ( table[sales] - CALCULATE ( [Avrsales], ALLSELECTED ( Table[Month] ) ) )
)

[Ashish_Mathur]

Hi,

You should create a Calendar Table with a relationship (Many to One and Single) from the Date column of the table to the Date column of the Calendar Table.  In the Calendar Table, create calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Month name from the calendar Table.  Create a year slicer and select 2023.  Write these measures:

Total = sum(table[sales])

Average sales across all months = averagex(all(Calendar),[Total])

Diff = abs([Total]-[Average sales across all months])

MAD = SUMX(values(Calendar[Month name]),[Diff])

Hope this helps.

[Ritaf1983]

Hi @mahfav 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523