Pivot table Total

[#1]

Hi Everyone,

I am using a pivot table and I am tying to include below expression.

=(If(Sum(CASE) >0 and PROD='C',Count(DISTINCT MATERIAL),0))

The expression works correctly on every row, but the total is not correct. The total includes everything which is not satisfied by the if condition. Can you please let me know, the correct way? Thanks in advance.

Thanks

Santhosh

[settu_periasamy]

can you tell us the dimensions? post the sample also

[Gysbert_Wassenaar · Accepted solution]

At the chart level, i.e. the total level, PROD has no single value. That's why that part of the condition will never be true at the total level. If you want to sum the counts of the row levels you need to use something like:

sum(aggr((If(Sum(CASE) >0 and PROD='C',Count(DISTINCT MATERIAL),0)), Dim1, Dim2, ..., DimN)

Replace Dim1, Dim2, ..., DimN with the names of the fields that you use as dimensions in your pivot table.

[sunny_talwar]

Try this:

=Sum(Aggr((If(Sum(CASE) >0 and PROD='C',Count(DISTINCT MATERIAL),0)), YourChartDimensions))

[#5]

Thanks Settu, Sunny and Gysbert. The solution worked.