Skip to Content
0
Jun 15, 2018 at 04:06 PM

Custom summary calculation on Excel Reports and Interactive Analysis

188 Views Last edit Jun 20, 2018 at 07:50 AM 2 rev

Hi community,

I have a demand to create a column that represents a percentage calculation between two other columns in the same hierarchy level, as exemplified in the image below.

The customer uses SAP Business One with a HANA model. The cube is generated from a calculation view, using MDX.

The column % Margine calcolata I have added with a simple calculation (Margine / Ricavo) and the values displayed are correct, as the customer demanded. However it is not inside the pivot, so changes in the columns require manual adjustments. The column % Margine was supposed to bring the same values as those of the columns % Margine calcolata, but inside the cube (it is a measure). It shows the correct results only for the lowest level of the report, which refers to the lines of the documents. The problem happens due to the automatic SUM of the pivot table functionality of excel when grouping, displaying a sum of the percentages, which reaches values not desired. The expected result is to have, even on the summary line, the same simple calculation between those two columns.

Is there a way to modify the cube or even the final excel pivot table, so that we can get the expected results mentioned above dynamically, without the need of any manual adjustments?

Is it possible to create this solution on SAP Lumira?

Best regards,

Bruno Milanez

Attachments

examplecube.png (14.2 kB)