Calculate average using month instead total count

[Anonymous]

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 

[v-xiaotang · Accepted solution]

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.

[Anonymous]

@v-xiaotang  thank  you so much. its working 

[Anonymous]

@amitchandak  i am getting Error sir in above measure 

[amitchandak]

@Anonymous , Try a measure like

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