Expression not considering dimensions in Pivot Table

[paulinhok14]

Hey good morning!

I'm trying to make a weighted average in a Pivot Table and the expression does not conform to the table dimensions, the values are repeated for all Customers, example of the problem:

The expression that I use to calculate the weighted average is:

=sum(total {<[Validacao Core] = {1}>}  Aggr( Count (Notification) * Core, Core ) ) /  sum(total {<[Validacao Core] = {1}>}  Aggr( Count (Notification), Core ) )

In another text object, the expression works perfectly and when I apply the Customer filters the values are recalculated there.

Otherwise, if I filter any client, the value displayed in the table is the correct one, for example:

I tried to include the break in the expression through <[Customer Name]> in front of the TOTAL identifier in the sum function, so the expression looks like:

=sum(total <[Customer Name]> {<[Validacao Core] = {1}>}  Aggr( Count (Notification) * Core, Core) ) /  sum(total <[Customer Name]>   {<[Validacao Core] = {1}>}  Aggr( Count (Notification), Core) )

But in this way, some values are null and other clients have values that do not make sense, as shown in the picture below, any suggestions on where I'm wrong?

In addition, I would like to make a calculation of standard deviation of this weighted average, the concepts to be applied are the same as the solution to this problem?

Thanks in advance!

[ogautier62]

Hi,

could you provide some rows example please ?

first thing I see is to add customer name before core in aggr

for null values, to see with an example, maybe necessary to add nodistinct in aggr

regards

[paulinhok14]

Hello, thanks for your answer!

Sure, it follows as an attachment...

I tried to put <[Customer Name]> before core in aggr and it doesn't work

[jaibau1993 · Accepted solution]

Hi Paulo. I have noticed two issues in your expression:

• First, I think you should Aggregate also by your table dimensions. You aggregate by Core but you need to agregate by Customer name too.
• Second, the column Media takes the same value for all customers because you used "TOTAL" in your sums

Taking the previous into account, try the following expression:

=sum({<[Validacao Core] = {1}>}  Aggr( Count (Notification) * Core, Core, [Customer Name] ) ) /  sum({<[Validacao Core] = {1}>}  Aggr( Count (Notification), Core, [Customer Name] ) )

Hope it helps. Bests,

Jaime.

[jaibau1993]

This is what I obtain, by the way:

[paulinhok14]

Hi Jaime...

First of all, thank you for your answer, this is exactly what I needed..

I thought that putting <[Customer Name]> in the Set Analysis Expression in sum() function it would break my results by Customer...

Thank you man, this helped me a lot!