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?
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)
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!
