2 Replies Latest reply on Nov 14, 2019 9:01 AM by aPsikus

# Percentage share based on Grand Total

We have a report:

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

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.

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

Is it possible in OBIEE?

• ###### 1. Re: Percentage share based on Grand Total

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)

• ###### 2. Re: Percentage share based on Grand Total

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!