AnsweredAssumed Answered

How can I calculate the weighted averages based on multiple groupings?

Question asked by B816T5YG on Apr 1, 2019
Latest reply on Apr 8, 2019 by Ana Gonzalez

I asked a similar question a few days ago (https://community.hitachivantara.com/message/43497), which I was able to resolve. However, that was merely to check whether the calculations were possible and was a smaller part of a larger problem

 

In order to calculate one of the weighted averages, the sum of a particular group is required, there can be many different groups.

 

An example of one of the groupings is by counterparty and term type. Suppose there is a choice of 7 counterparties and 7 term types, this can result in 49 different grouping permutations records.

 

I then need to calculate the sum of a particular value of each group, (as well as maintain the group count) before calculating the weighted average.

 

My approach could be all wrong, but performing the calculation for every group is proving to be quite difficult, I’m not sure if this can be done as a single transformation.

 

I apologise if I have provided a vague explanation, and will provide as much as I can if you require more information.

 

Here's an image of my (incomplete) transformation, with some notes of what I'm trying to achieve; each path contains its own set of groups.

grouping.PNG

Any help with this will be immensely appreciated.

 

Update:

Here's an example of input data and groupings:

 

example.PNG

Outcomes