Add an average column

[Anonymous]

I'm currently want to add a column which is the average of total amount over 12 months. As for the previous part, I tried to use what I've guided in Power BI's community to obtain the "New measure" of total sum, and I've put it under “Values". The formula for sum_ is

sum_ = sum('2023 RM Raw Data'[Amount])

Therefore, I wonder how to create an average? If I try to use average = sum('2023 RM Raw Data'[Amount])/12, I'll get 12 average followed by each month, which is weird and not the desired result. Thus, how to solve this issue?

[Anonymous]

Hi @Anonymous ,

Has your problem been solved?

If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

I noticed that your formula could be modified as:

calculate(sum('2023 RM Raw Data'[Amount]), filter(allselected('2023 RM Raw Data'),[groupfield]=max('2023 RM Raw Data'[groupfield]))/12

filter(allselected('2023 RM Raw Data'),[groupfield]=max('2023 RM Raw Data'[groupfield])

The above is to calculate the sum of a whole year in groups. You can modify this based on the fields in the rows in the matrix.

Best Regards,

Stephen Tao

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

Best Regards,

Stephen Tao

[Ritaf1983]

Hi @Anonymous 

Please refer to the linked video:

https://www.youtube.com/watch?v=HEMARcoxhkI

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