Average Weighted Calculation by Month

[Anonymous]

Good morning

I have a data table that has the following information. The Density column is calculated Kg/ M3

My	Kg	M3	Density
January	950	143.464776	6.62183448
January	1600	222.139008	7.20269715
January	1600	153.788544	10.4038959
February	1600	172.1412	9.29469529
February	1600	165.53628	9.66555489
February	3200	432.886272	7.39224181
February	800	66.214512	12.0819436
March	1600	165.53628	9.66555489
March	974	154.500528	6.30418558
March	850	85.43808	9.94872544
March	800	62.654592	12.7684177
March	1750	264.858048	6.60731291

I need to calculate the weighted density, in excel I do it by creating these separate columns (% Total and Den Pond):

My	Kg	M3	Density	% Total	Den Pond
January	950	143.464776	6.62183448	0.22891566	1.51584163
January	1600	222.139008	7.20269715	0.38554217	2.77694348
January	1600	153.788544	10.4038959	0.38554217	4.01114058
February	1600	172.1412	9.29469529	0.22222222	2.06548784
February	1600	165.53628	9.66555489	0.22222222	2.14790109
February	3200	432.886272	7.39224181	0.44444444	3.28544081
February	800	66.214512	12.0819436	0.11111111	1.34243818
March	1600	165.53628	9.66555489	0.26782725	2.588699
March	974	154.500528	6.30418558	0.16303984	1.0278334
March	850	85.43808	9.94872544	0.14228323	1.41553676
March	800	62.654592	12.7684177	0.13391363	1.70986511
March	1750	264.858048	6.60731291	0.29293606	1.93552019

The % Total column is the result of dividing the kg of each row by the total kg per month, for example the first value of 0.2289 in the first row is made by dividing 950 / (950+1600+1600) x 0.2289 and so with all January rows. For the first row of February it has a value of 0.2222 is the result of 1600 / (1600+1600+3200+800) x 0.2222 and so on each month.

The Den Pond column is the result of multiplying the Density * % Total column, for example in the first row is 6.62 * 0.22 x 1.51 and so each row.

I wish I could have a table with the following information

My	Dense
January	8.30392569
February	8.84126791
March	8.67745446

In which the Densi column is the sum of the Den Pond column for each month.

Can someone help me?

[v-stephen-msft · Accepted solution]

Hello @omarbobadilla ,

You should test the calculated column instead of the measure.

The calculated column creates the Den Pond column.

More details can be found here.

Column calculated versus custom

Best regards

Stephen Tao

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Thank you very much for the solution, it was exactly what I needed

[Anonymous]

[Your Table] =
ADDCOLUMNS(
    DISTINCT( T[Month] ),
    "Dense",
        CALCULATE(
            var __total = SUM( T[Kg] )
            var __dense =
            SUMX(
                T,
                var __density = T[Density]
                var __kg = T[Kg]
                var __percentTotal =
                    DIVIDE(
                        __kg,
                        __total
                    )
                var __denPond =
                    __percentTotal * __density
            )
            return
                __dense
        )
)

[Anonymous]

It makes me error:

Adjust the fields but I get error... Funny thing is, I don't get the variable __dense

What's it all about?