Add calculated columns in Pivot Table

User294199

Hi,

I have following Pivot Table :

Now For each Month Column, selected dinamically from a prompt I want add a new column with the following formula:

January % =  row Category value / Grand Total * 100

The same for other Months.

The output should be like the following:

I've tried calculated elements and other stuff, but it doesn't work. How i can do? It's important the layout.

Can you help me?

Thanks

Giancarlo

Jerry Casey

The simplest way is to duplicate the column in your pivot table, select the edit option on the duplicate,, and select "Display value as percent of column".  You can edit the header of that column to read "Month Pct", or whatever.

Gianni Ceresa

Exactly what Jerry said.

You say the layout is important, but you aren't in Excel and therefore you have to go with how OBIEE work in a pivot table.

The "Grand Total" will also calculate for the % column and the name of the 2 measures will be visible (as you more than 1).

User294199

Hi,

an important thing is that Year and Month aren't static but dinamic. They can be chosen by user using two prompts. So the user can select multiple years and months, or only particular month. So, for year 2020 I can have only January, February, Avril month. So need a single measure column that automatically OBIEE duplicates for all months selected by user and they have to be placed near each # column.

Where i have to place the measure? On columns, on rows, or on measures section?.

Thanks

Gianni Ceresa

Your year/months are dimensions, so you duplicate the measure you currently have and one you keep it as it is, the second one you do the "show value as...".

The 2 measures (the same one in double) goes in measures, and you then move the "Measure Labels" in the Columns part and you place it after the month (so the 2 columns of # and % are next to each other for every month).

User294199

Thanks a lot!!!! I marked as Exact Answer

Giancarlo