Calculate the average normalized score in Beast Mode
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.
Best Answer

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 preaggregate your data in an ETL before attempting to do an aggregate within a beast mode.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers

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.
If I solved your problem, please "accept" my answer as the solution
1 
The FIXED function can be used to get the MAX score on a product basis:
MAX(MAX(
Total Skill Score
) FIXED(BYProduct))
The normalized Score could be calculated as
Total Skill Score
/MAX(MAX(Total Skill Score
) FIXED(BYProduct))
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
andMax Product Group Score
as additional columns to the dataset so you can operate with them with easier formulas once working on the cards themselves.1 
@marcel_luthi agreed on the complexity. That is why I was thinking ETL over beastmode(s)  especially for #5.
If I solved your problem, please "accept" my answer as the solution
0 
@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. :(
0 
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 preaggregate your data in an ETL before attempting to do an aggregate within a beast mode.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
 All Categories
 1.1K Product Ideas
 1.1K Ideas Exchange
 1.2K Connect
 970 Connectors
 259 Workbench
 Cloud Amplifier
 1 Federated
 2.4K Transform
 76 SQL DataFlows
 502 Datasets
 1.8K Magic ETL
 2.7K Visualize
 2.2K Charting
 379 Beast Mode
 21 Variables
 487 Automate
 104 Apps
 379 APIs & Domo Developer
 6 Workflows
 22 Predict
 6 Jupyter Workspaces
 16 R & Python Tiles
 319 Distribute
 67 Domo Everywhere
 252 Scheduled Reports
 59 Manage
 59 Governance & Security
 1 Product Release Questions
 5K Community Forums
 37 Getting Started
 23 Community Member Introductions
 64 Community Announcements
 4.8K Archive