Custom aggregation in specific set of rows

Waheguru

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

Catalina

Hi @Waheguru
,

You could use a flow like below:

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

 

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

 

Add the calculated sum values to the dataset:

 

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

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