Oracle Fusion AI Data Platform Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Fusion AI Data Platform Forum

Categories

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

How to calculate the distinct sum of a spend by a category?

Received Response
121
Views
3
Comments
JobDeLuna
JobDeLuna Rank 5 - Community Champion
edited Aug 13, 2024 7:39PM in Oracle Fusion AI Data Platform Forum

Hi,

How to calculate the distinct sum of a spend by a category?

So basically, I want to have the distinct base spend 2 by CONCAT3

I am aiming to have the Base Spend 2 Summary result I boxed in red below.


SUMX.png


Tagged:

Answers

  • JoseP-Oracle
    JoseP-Oracle Rank 3 - Community Apprentice

    @JobDeLuna - For the Base Spend 2 SUMMARY, add again the Base Spend 2 column in the visualization. Then go to the Properties view of the visualization -> Values -> select the added column and change the Aggregation Method property to Average Distinct . This will result in the desired outcome.

    See example below (column SUMMARY):

    sample2.png


  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Thanks @JoseP-Oracle ! How about the grand total? I need to create a tile visualization of the sum of the 'Base Spend 2 SUMMARY'. On your example, I need to total 23, 34 and 46 having a total of 103.

  • JoseP-Oracle
    JoseP-Oracle Rank 3 - Community Apprentice
    edited Oct 23, 2023 5:10PM

    The Grand Totals follow the aggregation rule of the column values (Average Distinct) hence the total will give the average and not the sum. There is not possible to override the aggregation type just for the Totals/Grand Totals.

    You can enter a request at the Oracle Analytics Cloud and Server Idea Lab for having an enhancement to have Sum Distinct in the options for Aggregation Method in the canvas visualization, and this voted by other users and reviewed by our product team.