Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate average using month instead total count

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 

 

 

 

Help appreciated
Help me
Help me please
Help Requested
Help required
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try a measure like

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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      v-xiaotang  thank  you so much. its working