Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Count formula in percentage calculations

Received Response
1
Views
5
Comments
Aluros
Aluros Rank 1 - Community Starter
edited Aug 13, 2024 6:12PM in Oracle Analytics Cloud and Server

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

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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?

  • Aluros
    Aluros Rank 1 - Community Starter

    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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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. 

  • Aluros
    Aluros Rank 1 - Community Starter

    I agree, Thomas

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

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