topic Re: Calculate average using month instead total count in DAX Commands and Tips

Calculate average using month instead total count

Anonymous — Thu, 06 Jan 2022 13:59:17 GMT

Hi , I am trying to get Month differnce values based total value average . when i created average dax measure after that applied in table . getting error.

i have attached sample PBI file

Sample File

In excel sheet i have totally 8 count and total actuals value 17,377.944 . its average as per the excel

= 17.377.944 / 8

avg = 643.62.

instead of i am want get average total month. i am used below measure

avg val = CALCULATE(SUM(Sheet1[Actuals]),FILTER(Sheet1,Sheet1[Year]="2020"))/12

please find the below screen shot .

i am to avg total value used all month like below

My formula

avg= calcuilate (SUM(Sheet1[actuals] - sheet[avg val]))/ Sheet1[actuals]

why i am getting every month different value , want use total avg for all month . looking for support,. thanks in advance

Re: Calculate average using month instead total count

amitchandak — Thu, 06 Jan 2022 14:39:47 GMT

Anonymous , Try a measure like

CALCULATE(averageX(summarize(Sheet1, Sheet1[Year],Sheet1[Month], "_1", SUM(Sheet1[Actuals]),FILTER(Sheet1,Sheet1[Year]="2020")) ), [_1])

Re: Calculate average using month instead total count

Anonymous — Sun, 09 Jan 2022 13:12:26 GMT

amitchandak i am getting Error sir in above measure

Re: Calculate average using month instead total count

v-xiaotang — Tue, 11 Jan 2022 06:23:14 GMT

Hi Anonymous

Try this,

DAX code:

test1 = var _totalEachYear= CALCULATE( SUM(Sheet1[Actuals]),ALLEXCEPT(Sheet1,Sheet1[Year])) return IF(SUM(Sheet1[Actuals])>0,DIVIDE(_totalEachYear,12))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate average using month instead total count

Anonymous — Tue, 11 Jan 2022 09:56:39 GMT

v-xiaotang thank you so much. its working