Custom aggregation in specific set of rows

Options
Waheguru
Waheguru Registered Posts: 1

Please see an example of data table I have.

I would like to convert the absolute counts in table 1 into percentages such that denominator is total count of all categories in each account as shown in table 2 below. Please help

Table 1 (Current Format)

Accounts

Category

Count

Account A

X

95

Account A

Y

100

Account B

Z

500

Account B

D

800

Table 2 (Desired Format)

Accounts

Category

% Count within each account

Account A

X

=95/195

Account A

Y

=100/195

Account B

Z

=500/1300

Account B

D

=800/1300

Answers

  • Catalina
    Catalina Dataiker, Dataiku DSS Core Designer, Registered Posts: 135 Dataiker
    Options

    Hi @Waheguru
    ,

    You could use a flow like below:

    Screenshot 2023-02-01 at 09.47.10.png

    First use a Group recipe to calculate the total count for each account:

    Screenshot 2023-02-01 at 09.54.27.png

    Then use a Join Recipe between the output dataset of the Group recipe and the input dataset:

    Screenshot 2023-02-01 at 10.03.58.png

    Add the calculated sum values to the dataset:

    Screenshot 2023-02-01 at 09.59.04.png

    Then calculate the percentage in a post-join computed column of the join recipe: 

    Screenshot 2023-02-01 at 09.46.56.png

    After these steps the output dataset contains the corresponding percentages for each account:

    Screenshot 2023-02-01 at 10.10.32.png

Setup Info
    Tags
      Help me…