Calculated column totals incorrect — Oracle Analytics

Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Calculated column totals incorrect

Accepted answer
53
Views
4
Comments
skhan
skhan Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence

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

Best Answer

  • Riyaz Ali-Oracle
    Riyaz Ali-Oracle Rank 6 - Analytics Lead
    Answer ✓

    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

Answers

  • Gaurav Bharadwaj-Oracle
    Gaurav Bharadwaj-Oracle Rank 6 - Analytics Lead

    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
    skhan Rank 3 - Community Apprentice

    @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
    crystal.espina Rank 1 - Community Starter
    edited Jul 12, 2025 7:29AM

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