5 Replies Latest reply on Sep 26, 2016 1:35 PM by Thomas Dodds

# Count formula in percentage calculations

Hi!

I am wondering how to create a numeric Count variable in Oracle BI.

For instance, I have one string column that contains different types of invoices (X or Y or Z). Is it possible to count how many types of X invoices I have? I would like to find percentage of X-type invoices in total amount of invoices (X+Y+Z), how can I do it in case of having string variable? Error code

What I have tried so far is formula CASE WHEN in order to replace X-type invoices with 1 and everything else with 0. However, it is not possible to find SUM of these "dummy" variables

• ###### 1. Re: Count formula in percentage calculations

Several things.

1.) Obviously you'll need a level-based measure which represents the total. Do you have that?

2.) Do you want one new measure called "%" which dynamically reacts to a dimensional attribute X/Y/Z added to the analysis as an axis or are you expecting 3 measures for X, Y and Z respectively and the attribute won't be used at all?

• ###### 2. Re: Count formula in percentage calculations

Hi Christian,

1. I have splitted the column with different types of invoices into two in a pivot table. In the first column I have only X-type invoices, in the second one - all others. I have counted number of rows in grand totals for the pivot table, so basically I can calculate % = Count of X-type invoices / Count of (X-type + other) manually, but I do not know how to do it automatically.

Could you please explain how to make a level-based measure which represents the total?

2. I want separate measures, in particular only % of X-type invoices in the total amount of invoices, I do not need a dimensional attribute.

Thank you

• ###### 3. Re: Count formula in percentage calculations

Every fact table should be built with the Count column having a 1 default value ... use of information drives physical design.  Having that count column makes your life so much easier.

• ###### 4. Re: Count formula in percentage calculations

I agree, Thomas

• ###### 5. Re: Count formula in percentage calculations

It's DW 101 stuff ... so have your team get it done!