Calculate the average normalized score in Beast Mode

Manasi_Panov

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.

3. Add up the normalized scores across all Products for each employee.

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.

Please help me with the formula.

GrantSmith

@Manasi_Panov

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.

ColemenWilson

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.

marcel_luthi

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.

ColemenWilson

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

Manasi_Panov

@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.

@marcel_luthi

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

GrantSmith

@Manasi_Panov

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.