Custom aggregation in specific set of rows

Waheguru
Level 1
Custom aggregation in specific set of rows

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

 

 

 

0 Kudos
1 Reply
CatalinaS
Dataiker

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

0 Kudos

Labels

?
Labels (3)
A banner prompting to get Dataiku