Percentage share based on Grand Total — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Percentage share based on Grand Total

Received Response
145
Views
2
Comments
aPsikus
aPsikus Rank 6 - Analytics Lead
edited Aug 13, 2024 5:21PM in Oracle Analytics Cloud and Server

We have a report:

# of Workers by 4 dimensions: Region, Month, Age Bucket and Gender presented as Pivot table.

base.JPG

I would like to calculate Percentage share per Gender for each month based on the grand total for each month to present kind of trend change for each month.

calc.JPG

I have tried several options from "Show data as", but none displayed expected results.

Is it possible in OBIEE?

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I'm not sure why none of the "show data as" options didn't work, but you can do it by creating a 3 "New Calculated Items" on Gender.
    The calc would be 100*'Female'/('Female'+'Male'+'Not Declared'), with the same for the other genders.
    Then, go into the Gender column properties and create a conditional data format for when Gender IS NULL. Make it percentage, with however many decimal places you want.

    EDIT: Sorry, I skimmed too quickly.  The above gets you gender pct by age bin at all subtotal and grand total levels.  It looks like you need a calculated column
    along the lines of 100*SUM(Workers by Region, Month, Age Bucket and Gender)/SUM(Workers by Month)

  • aPsikus
    aPsikus Rank 6 - Analytics Lead

    Jerry,

    You're DA MAN!!!

    Just one correction to the proposed formula:

    Using: 100*SUM(Workers by Region, Month, Age Bucket and Gender)/SUM(Workers by Month) calculates same percentage in all rows.

    After small correction: 100 * Workers / SUM (Workers by Month) calculates as required.

    Many thanx!