Bug 152608

Summary: Pivot table Displayed value % of Row shows wrong result
Product: LibreOffice Reporter: andis.lazdins
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: RESOLVED NOTABUG    
Severity: normal CC: andis.lazdins
Priority: medium    
Version: 7.4.3.2 release   
Hardware: All   
OS: All   
Whiteboard:
Crash report or crash signature: Regression By:
Attachments: Sample file

Description andis.lazdins 2022-12-20 08:52:29 UTC 
Description:
Selection of Displayed value "% of Row" in Pivot table in attached example shows wrong result, in spite the Total column show 1100%.

Steps to Reproduce:
1. Open attached file and select data set A1:K601.
2. Select insert Pivot table and put Vietas ID2 into Row fields, Dziļums, cm into Column fields and Paraugu masa, g in  Data fields. In Column and Row fields hide empty values.
3. For Data fields select average and % of Row in Displayed value.

Actual Results:
Values in table exceeds 100% in spite in Total column 100% is apearing

Expected Results:
Sum of values in row is 100%


Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 7.4.3.2 / LibreOffice Community
Build ID: 1048a8393ae2eeec98dff31b5c133c5f1d08b890
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: lv-LV (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 1 andis.lazdins 2022-12-20 08:53:11 UTC 
Created attachment 184255 [details]
Sample file
Comment 2 Mike Kaganski 2022-12-20 09:23:25 UTC 
As explained in help for the Data Field dialog [1]:

> % of row
> 
> Each result is divided by the total result for its row in the pivot table.
> If there are several data fields, the total for the result's data field is used.
> If there are subtotals with manually selected summary functions, the total with
> the data field's summary function is still used.

This means that the percent value shown in the cell depends on the "total": first, the cells' *absolute* value and "total" is calculated, then the absolute value in each cell in the row is divided by the total, to get the percentage. And indeed, the absolute "total" for a row showing an average is the *total* average; and each cell's value is some local average, that may deviate from total in any direction, including a case when it's greater than the total average. When percentage is calculated from that, the percentage would be greater than the total.

Again: the "total" here is not a sum of percentages in the row cells, but an average for the whole data used for the row.

The values above and below 100% allows one to see easily, how much this cell deviates from the total average. This is much easier to see that 226% is 2.2+ times larger than the total average, compared to seeing '0.0377' in a cell, and having to scroll right to see the '0.0166' total average for the row, then evaluating the ratio (if one didn't use percentages).

[1] https://help.libreoffice.org/7.4/en-US/text/scalc/01/12090105.html?DbPAR=CALC