Forum Discussion

alevandenes's avatar
alevandenes
Helper IV
2 years ago
Solved

Use Average in a column

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?

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

3 Replies

  • sergej_og's avatar
    sergej_og
    Super User
    2 years ago

    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's avatar
      alevandenes
      Helper IV
      2 years ago

      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)

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.