How to calculate a sum of averages ?

[oroussel]

Dear PBI community

As introduction, I'm new user that need help as I did not find on forum. my request is the opposite of tradionnal "average of sums..."

Here we go : I created a matrix to get average prices ("prix" calculted by "Val €" / "Qtes T" (see below)

The 3 lines represents successives operations of same product transformation. Consequently, to get the total price, I need to sum the individual prices. Means that, instead of returning 65.33€, I'm expecting to get 148.66€ (sum of 81.45 + 52.21 + 15.00). I tried to create new measures or columns but failed to create the right formula...

In second step, "Qtés" total could be also re-calculated also based on formula "Val €" / "Price €") to get a coherent information but I'm not sure this is possible...

Thanks for your help !

Olivier

[MFelix · Accepted solution]

HI @oroussel,

The Prix is a measure?

When you use a measure the value are always recalculated based on the variable you have so in the total you will have:

-165352 € / 2531 = 65.33 that it's the value you are getting.

You need to add the same formula but as a clomun and then add it to you matrix and you will get the 145 since making it a column it wil do the calculation for each row individually independtly of the variable you put in the Matrix.

Regards,

MFelix

[MFelix · Accepted solution]

HI @oroussel,

The Prix is a measure?

When you use a measure the value are always recalculated based on the variable you have so in the total you will have:

-165352 € / 2531 = 65.33 that it's the value you are getting.

You need to add the same formula but as a clomun and then add it to you matrix and you will get the 145 since making it a column it wil do the calculation for each row individually independtly of the variable you put in the Matrix.

Regards,

MFelix

[oroussel · Accepted solution]

Hi MFelix

That worked! I added a column as recommended but also modified my formula by deleting the SUM function intially included. I'll try now to play with colums features to discover more!

Many thanks for quick answer

Olivier