calculate avarege cost

[andreaturri27]

Hi community, 

i would calculate avarege cost into onther column.

avarege cost for 2017 is 207,20 SUM (INVRSN, posting date 2017) = (207.20+207.20+207.20) / (1+1+1)

avarege cost for 2018 is 207.20 SUM (INVRSN, posting date 2017/2018) =(207.20+207.20+207.20+207.20+207.20+207.20) / (1+1+1+1+1+1)

avarege cost for 2019 is 221.60 SUM (INVRSN, posting date 2018/2019) =(207,20+207.20 +693.60)/(1+1+3)

How with dax?

thank you.

[V-pazhen-msft]

@andreaturri27 

You should use Allexcept in this case becasue you want two year average.

You can create either measure or column, see the dax with my sample:

Measure = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALL('Table'),SUMX(FILTER('Table',EARLIER('Table'[Posting date].[Year])='Table'[Posting date].[Year]||EARLIER('Table'[Posting date].[Year])='Table'[Posting date].[Year]-1),1)))

Column = CALCULATE(AVERAGE('Table'[Value]),FILTER('Table','Table'[Posting date].[Year]=EARLIER('Table'[Posting date].[Year])||'Table'[Posting date].[Year]=EARLIER('Table'[Posting date].[Year])-1))

 

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

[andreaturri27]

Hi @V-pazhen-msft ,

thank you for the replay but the value on row is not correct.

i use 

CALCULATE(AVERAGE(MA_InventoryEntriesDetail[LineAmount]);FILTER(ALL(MA_InventoryEntriesDetail);SUMX(FILTER(MA_InventoryEntriesDetail;EARLIER(MA_InventoryEntriesDetail[PostingDate].[Anno])=MA_InventoryEntriesDetail[PostingDate].[Anno]||EARLIER(MA_InventoryEntriesDetail[PostingDate].[Anno])=MA_InventoryEntriesDetail[PostingDate].[Anno]-1);1)))

but the total correct for  year 2017 --> 207,200 , for  year 2018--> 207,200 for  year 2019 --> 221,600 for year 2020 --> 221,600

[V-pazhen-msft]

@andreaturri27 

It seems you have used show in SUM for LineAMount, where my example showed in Dont Summarize, Can you share that sample pbix so I can get a look into it for an absolute solution.

Paul

[andreaturri27]

hi @V-pazhen-msft thank you for the replay click here to download the file. 

Andrea

[amitchandak]

I think you use allexcept. Create a year column that would be better

refer : you need on allexcept part of it

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

[andreaturri27]

Can you expand on that idea with with an example please @amitchandak ?

thank you

Andrea