Oracle Fusion AI Data Platform Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Fusion AI Data Platform Forum

Categories

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

Is there a way to get the difference between 2 rows as a Grand Total

Accepted answer
42
Views
5
Comments
SharonAtGorilla
SharonAtGorilla Rank 4 - Community Specialist
in Oracle Fusion AI Data Platform Forum

I know that the msum function can get that, but I need to display it as a column total not another column.
For instance using the Financials -GL Balance Sheet Subject Area and using Fiscal year

Fiscal Year #Cash
2024 50,000
2025 48,000
Grand Total 2,000

Tagged:

Best Answer

  • SharonAtGorilla
    SharonAtGorilla Rank 4 - Community Specialist
    Answer ✓

    This worked for me. Row_count was a good clue. Thank you.

    case COUNT(DISTINCT "Financials - GL Balance Sheet"."Time"."Fiscal Year")
    when 1 then "Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"
    when 2 then

    sum(
    case "Financials - GL Balance Sheet"."Time"."Fiscal Year"
    when year(CURRENT_DATE) then "Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"
    when year(CURRENT_DATE)-1 then "Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"*-1
    end
    )
    end

Answers

  • RVohra
    RVohra Mod

    Can you see if you can use following formula in a new measure :

    CASE
    WHEN GROUPING("Fiscal Calendar"."Fiscal Year") = 1 THEN
    MAX("Balances"."Ending Balance")
    - MIN("Balances"."Ending Balance")
    ELSE
    "Balances"."Ending Balance"
    END

  • SharonAtGorilla
    SharonAtGorilla Rank 4 - Community Specialist
    edited Dec 2, 2025 10:06AM

    case when Grouping("Financials - GL Balance Sheet"."Time"."Fiscal Year") = 1 then
    max("Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash")- min("Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash")
    else
    "Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"
    end

    I get this error

    Function Grouping is not defined by administrator.

  • RVohra
    RVohra Mod

    How about ROW_COUNT? Can you try this one …

    case when row_count("Financials - GL Balance Sheet"."Time"."Fiscal Year") > 1 

  • RVohra
    RVohra Mod

    @SharonAtGorilla , I am very glad to see your issue is resolved. As per community guidelines, appreciate if we can mark the Answer as Accepted Answer. It will help the community for future references. Thanks