Why does AGGR() return different results than direct aggregation for percentage calculations in pivot table hierarchy?

[akmalquamri]

Hi Experts,

I have dataset looks like below table:

There is another table having columns Region > Zone > Country.

The problem I am facing is that in pivot table I have hierarchy Region > Zone > Country > Shopkeeper. With this hierarchy, my below two codes were working fine

Code1:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Region', 'Zone', 'Country', 'Shopkeeper'),

Sum(global shop distribution)/sum(Total <region> global shop distribution),

Sum(global shop distribution)/sum(Total <zone> global shop distribution),

Sum(global shop distribution)/sum(Total <country> global shop distribution),

Sum(global shop distribution)/sum(Total <country> global shop distribution),)

code2:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Region', 'Zone', 'Country', 'Shopkeeper'),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Region> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Zone> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Country> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Country> global shop distribution), Region, Zone, Country, Shopkeeper))

)

As I change the hierarchy to shopkeeper > region > zone > country first code is running properly but second code is showing different answers.

Code1:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Shopkeeper', 'Region', 'Zone', 'Country'),

Sum(global shop distribution)/sum(Total < Shopkeeper> global shop distribution),

Sum(global shop distribution)/sum(Total <Region> global shop distribution),

Sum(global shop distribution)/sum(Total <Zone> global shop distribution),

Sum(global shop distribution)/sum(Total <Zone> global shop distribution))

code2:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Shopkeeper', 'Region', 'Zone', 'Country'),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Shopkeeper> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Region> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Zone> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Country> global shop distribution), Region, Zone, Country, Shopkeeper))

)

I need this because I want to get sum of these calculations at above level plus multiplying these with master item of another calculation. I want to fix this AGGR calculating then my problem will get resolve.

If any suggestions of different approaches will be highly appreciated.

Thanks for your attention.

[Daniel_Castella]

Hi @akmalquamri 

Is not there a discrepancy on your code?

In the first code 1 and code 2, the totals are done by Region, Zone, Country, Country. 

In the second code 1, the totals are done by Shopkeeper, Region, Zone, Zone; but code 2 is Shopkeeper, Region, Zone, Country.

The second code 2 doesn't seem to follow the same pattern as the others. Is that a typo issue or is it done on purpose?

Kind Regards

Daniel