Use Average in a column

[alevandenes]

Hi community,

I have this table below.

I would like to be able to fill in the costs column also for future months, by multiplying the units (that i already have) with the average of the avg cost per unit until now (independent of timeperiod).

I managed to calculate that in a measure 

avg = CALCULATE(AVERAGE('AVG cost per unit'[avg cost per unit]), REMOVEFILTERS('AVG cost per unit'[Month Year]))

but i cant get it into the column. can someone help?

[Anonymous · Accepted solution]

Hi @alevandenes ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a calculated column.

Forecasted costs = [units] * AVERAGEX(FILTER('Table','Table'[Month Year]<=EARLIER('Table'[Month Year])),[avg cost per unit])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[sergej_og]

Hey @alevandenes ,
what is your target result you want to see? Provide a screenshot pls.
A calculated column?
Or a measure which you can use for further calculations?

Regard

[alevandenes]

Hi, basically i want to use the actual costs for past months (as you can see in the screenshot that column is filled in until August 2023) and i want to use forecasted costs per future months.

Forecasted costs= units * (average of the cost per unit from all the previous months)