Grand Total % values not matching with average of Column Percentages

[sekharQV]

Hi All,

I am struggling to understand how percentage values in Grand Total column are calculated in Pivot table.

I have attached the image and sample QVW file to make you understand my issue.

Actully user is asking about these grand totals are populated and i am not able to guide them.

I request you all, please let me know hoe these grantotals are calculated.Because it is not giving Average.

Also please let me know if i did any mistake while doing calculation.

Thanks in Advance,

Raja.

[Or]

Actually, the average is being calculated correctly. The reason you aren't getting a match for the displayed values is that you have "Suppress zero values" checked. If you uncheck that, you will notice that there are several cells with 0 impacting the overall average.

[sunny_talwar]

It might also help to break down your expression into the two pieces you have in there

Sum(OntimeFlag)

Sum(LateFlag)

Looking at the total, you will understand that 4/(4+2) = 66.67% for customer 1 and the reason you don't really saw this was because for some of the months (like Mar) the OntimeFlag was 0... so you have this calculation 0/<Number> which is essentially 0 as orsh_ mentioned that because your chart was suppressing 0, you didn't really see those 0's.

[sekharQV]

Hi Sunny,

Thank you very much for your prompt response.

As per your suggestion i have disabled "Suppress 0 values" and exported data to excel for validating data.

And i am able to get correct result for all customers except Customer1.

Surprisingly i am getting correct result for Customer1 when i am calculating average for only months of 2016.

I have attached the exported data excel with validated results.

Can you kindly please check and let me know your suggestions.

Thanks,

Raja

[sunny_talwar]

You can use this to get the Average

Avg(Aggr(sum(OntimeFlag)/(sum(OntimeFlag)+sum(LateFlag)), [Customer No], Year, Month))

[sekharQV]

Thank you Sunny for solution. But i am still i am wondering that why my earlier expression not giving correct result for Customer1.

if possible can you please enlighten how my earlier expression works.

[sunny_talwar · Accepted solution]

So the difference between the two expression is this

Avg(Aggr(sum(OntimeFlag)/(sum(OntimeFlag)+sum(LateFlag)), [Customer No], Year, Month))

Simply ignores this -> Sum(OntimeFlag)/(Sum(OntimeFlag)+Sum(LateFlag)) calculation for the total rows and look at the available numbers at the row level and find its average

On the other hand, this Sum(OntimeFlag)/(Sum(OntimeFlag)+Sum(LateFlag)) performs the same calculation at the total level also. So basically, on your total row, it will first find the Sum(OntimeFlag) and Sum(OntimeFlag) + Sum(LateFlag) and then perform the division. The best way to see this would be to create above two expressions and see what numbers you have in the total and do the division in Excel or a calculator.