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

    Percentage share based on Grand Total

    aPsikus

      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?

        • 1. Re: Percentage share based on Grand Total
          Jerry Casey

          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
            aPsikus

            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!