# Calculate the average normalized score in Beast Mode

Options
Member

Hello,

I have data in this format:

I want to perform the following calculation in Beast Mode and create a chart out of it:

1. For each Product, calculate the maximum possible score within that Product.

2. Divide the score of each employee (Name) in each Product by the maximum possible score for that Product. This is giving the Normalized score between 0 and 1 for each Product.

4. Calculate the average normalized score for each employee by dividing the sum by the total number of evaluation Products.

5. Multiply the average normalized score by the maximum possible score within that Product Group.

Tagged:

• Coach
Options

You're wanting to do an aggregation of an aggregation (SUM [step1] of MAX [step3]) which isn't possible within a beast mode. You can only do one level of aggregation. You'd need to pre-aggregate your data in an ETL before attempting to do an aggregate within a beast mode.

**Did this solve your problem? Accept it as a solution!**

• Coach
Options

I was able to do this in magic ETL.

First, use the group by tile to get the max skill score for each product:

Second, join that data back to your input data on product:

Third, use the formula tile to calculate the normalized score:

Then, in a card you can create a beastmode to get your average normalized score:

I am a bit confused on your final metric, what does that tell decision makers? Not sure what is being measured there.

• Coach
Options

The FIXED function can be used to get the MAX score on a product basis:

```MAX(MAX(`Total Skill Score`) FIXED(BY `Product))`
```

The normalized Score could be calculated as

````Total Skill Score`/MAX(MAX(`Total Skill Score`) FIXED(BY `Product))`
```

I'm concerned you're trying to get data at multiple aggregation levels and if you want to do that on a single chart this will be really complex, up to point 5 you're running this by employee, but then you want to run this by Product Group. All this sound doable with the FIXED formula as a Beast Mode, should it be responsive to filters applied to the card, if you want the MAX to be always from the full dataset, then I'd suggest you go with an ETL that will use a Group By Tile and Joins to add the `Max Product Score` and `Max Product Group Score` as additional columns to the dataset so you can operate with them with easier formulas once working on the cards themselves.

• Coach
Options

@marcel_luthi agreed on the complexity. That is why I was thinking ETL over beastmode(s) - especially for #5.

• Member
Options

@colemenwilson
I appreciate the suggestion of utilizing ETL, but it isn't suitable for this particular situation. The primary objective is to analyze and compare the Skill Levels of technicians or groups of technicians across various regions, company branches, and countries, considering different products and product groups. I apologize if my initial explanation was unclear.

Thank you for the suggestions, but this is not the complete solution in my case. :(

• Coach