nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

Calculation on two columns in pivot table

bjbreitling

I have a pivot table that has company on the vertical axis and account on the horizontal on the top like below:

company Income Gallons

comp1 630 4

comp2 588 7

I want to create an additional column that divides income by gallon. Can I have some help? I know there is a similar question in the discussion forum but I don't know what is meant by "column area" so some moderately detailed instructions on where to navigate to do this would be appreciated by this newbie.

Thanks!

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

You add a new column to your analysis and edit the formula to make your division of income by gallon there.

The new column will be available like your already exiting facts columns and by default will be added to your pivot.

(If you have no or little idea of what OBIEE is and how it works have a look at https://apexapps.oracle.com/pls/apex/f?p=44785:24:0:::24:P24_CONTENT_ID,P24_PREV_PAGE:12164,1 )

bjbreitling

Gotcha, I'll take the post down in the other discussion. I meant to edit and change to the enterprise one as that is where I've asked a few questions before.

bjbreitling

The tutorial is helpful but shows how to do a percent based on a column in the pivot table. How would one do a calculation based on two columns in a pivot table (one measure that is calculated for the row dimension and the column dimension?)

Gianni Ceresa

You add a new column to your criteria, you click on "edit formula", in the editor you enter your calculation referencing your columns (you find them in the list on the left or in the "column" button at the bottom).

bjbreitling

Ha you beat me to it Gianni! I did actually find this out on my own exploring the selection steps just a bit ago but you posted it. Thanks!

bjbreitling

It might be a little different in 12c though. I went to selection steps and clicked on the dimension thats the column in my pivot atbale and add a calculated item that was the first column of the dimension in my table divided by the second.

bjbreitling

Hi Gianni, I don't suppose you would know how to change the format of that new calculated item in the pivot table?

Gianni Ceresa

Well, a calculated item with selection steps is not the best approach for that because of how selection steps works (and where it happen) ...

Define "change the format" : what are you trying to do?

PS: "Income" and "Gallons" are attributes of a dimension in your model? Aren't they supposed to be measures?

bjbreitling

We have one measure called "measure". "income" and "gallons" are two accounts of the account dimension that gives the column header in the pivot table. The rows are given by the company dimension. The calculated item I did in the selection steps divides "income" by "gallon" income has a $ format while gallon doesn't. I'd like to give the income per gal calculated item the $ fromat too.

Upcoming Event

Analytics - Fusion Data Intelligence: Semantic Model Extensions in FDI: Implementation Patterns, Security, and Best Practices
Build on the foundational concepts from Part 1 with a deeper look at implementation patterns for extending the FDI semantic model. This session covers common use cases such as modifying subject areas, creating custom subject areas, combining external application models, and supporting custom security requirements. It also…

Top Leaders