Calculating percent of maximum value - DAX, modelling, calc column?

I have some experimental data where we measure how hard muscles are working during a bunch of running conditions for a bunch of guys we had doing the experiment.
This means we have the raw values (voltage) for each subject, for each running condition, for each muscle, expressed as the percent of the running cycle (heel strike is start and end of the running cycle - 1% to 100%)

What I’d now like to do is calculate new values from these raw voltages so that I can express the muscle activation as a percent of the maximum value I saw for that muscle, for that subject, across any of the different trials we did.
In Excel I’d just create a pivot table with the subject names in the rows, and the muscles in the columns, and find the max value for each of the cells. This max value is then 100%, and I create new columns and rows for each of the raw voltages which will now be from 0% to 100%.
Trying to get better at power BI, and thinking should I take this path (New table for max values, new table for all the data now expressed as % of max values), or would I be better to create a DAX measure to calculate this on the fly?
here’s how the data’s shaped:

and here’s the first few rows:

I suspect I might have to make entirely new data tables with all of these values expressed as percentage but that seems inefficient.
Any help gratefully accepted,

Hi Rod,

If you want to calculate the max value per subject and muscle - it seems to me muscle is another dimension (not yet present in your model). It would also make sense to me to unpivot your data so each muscle/sensor would become an Attribute and then use DAX to calculate these new percentages.

Here are some items you might find interesting:

1 Like