Calculated column totals incorrect

skhan

i am working on Margin analysis for the group of companies , in that i have calculated revenue column , calculated margin and Percentage of margin , individually margin percentages are shown correct but at grand total percentage column showing incorrect value, have tested all aggregation rules nothing works

Riyaz Ali-Oracle

Hi @skhan ,

Try Recalculating Margin % at Grand Total Level:

You need to create a separate calculated column for the grand total margin % using this logic:

1. Create a new measure column:
   sql
   (SUM("Margin") / SUM("Revenue")) * 100
2. Place this column in a narrative view or in a separate section of the dashboard.
3. Hide the incorrect aggregated margin % column from the grand total row if needed.

In BI Publisher (RTF Template)

Use this conditional logic in the footer or summary row:
xml

<?if@inlines:GROUPING("YourGroupName")=0?> <?format-number(SUM(Margin) div SUM(Revenue) * 100, '#.##')?>%<?end if?>

Thanks,
Riyaz Ali

Gaurav Bharadwaj-Oracle

Hi @skhan ,
You will have to elaborate a little here for us to understand and then assist you.
Can you please upload a document with screenshots explaining what is not working and what is the expected outcome according to you ?

Regards,
Gaurav

skhan

@Gaurav Bharadwaj-Oracle

Hi

My total margin is 77,015 and total revenue is 204,480. If I calculate the margin percentage manually, it's 37.6% (77,015 ÷ 204,480).

However, in my Pivot Table, the grand total for margin % is showing as 34.8%.

Here’s what I observed:

• Initially, revenue was a calculated column without using aggregation (SUM), so the totals were incorrect.
• After updating the revenue calculation to use SUM, the totals for revenue and margin became correct.
• But the margin percentage in the grand total row is still showing the old value (34.8%), not recalculating based on the correct totals.

So, the percentage grand total is not being recalculated based on the correct summed totals, even though the individual values are now correct.

crystal.espina

This issue had been plaguing me for months! Thank you @skhan and @Riyaz Ali-Oracle for helping me resolve this issue.