Incorrect total on pivot chart

[#1]

Hi,

I’ve created a pivot table to look at users and their total consumption for the date period.  Each user has various amounts of sites linked to them so what I have done is create the table with their name as the dimension and number of sites and consumption as the expressions.

When the report is collapsed, the consumption is incorrect but the number of sites are.
For example:

User	Sites	Consumption
User 1	2	162.74

But when I split the report into each site – the consumption will add up correctly.

i.e

User	Site Name	Sites	Consumption
User 1	Site 1	1	20.3
User 1	Site 2	1	30.4

The consumption value is the result of multiplying 2 fields together so I’m guessing that on the rolled up level the calculation is different because the values used are different. So what I need is the ‘rolled up’ total to be the same as the total of the pivot table if I was to expand it and show all the sites? Is there any way of achieving this?  Any help is much appreciated. Thanks.

[Gysbert_Wassenaar · Accepted solution]

Try changing your expression to sum(aggr( ...original_expression_here..., User, [Site Name], Site). Replace the field names with the actual case sensitive field names of the dimensions of your pivot table.

[#3]

That worked!

Thanks Gysbert