How to Create a Table with Individual Thresholds in Power BI

How to create a table in Power BI with individualized thresholds for our players

In this post, we'll walk you through how to create a table with individualized thresholds for our soccer players using Power BI. Specifically, we'll look at individual acceleration and deceleration thresholds, but it can also be used to analyze metabolic speeds or powers. At the bottom of this post, you'll find the Power BI file with the table, which you can edit.

If you'd like to read the original post where I explain how I derived the individual ACC&DEC thresholds and what I used them for, I refer you to the specific post.

https://www.thefootballpitch.one/en/post/individual-acceleration-deceleration-thresholds-football

Step 1 - File Upload

In my case, I created an Excel spreadsheet containing a table (see photo) with 7 columns. Specifically , Player, Date, Max Speed, Max ACC, Max DEC, Image, Role.

Step 2 - Calculate the P95 Percentile Score

The next step is to determine the ACC/DECmax value on which to calculate the different thresholds of our players.

By using the percentile function we will determine a new measure, in our case the value based on all the matches recorded for each individual player.

Soglia 95 Percentile ACCmax = 
PERCENTILEX.INC(
    'Nome del vostro foglio dati', 
    'Nome del vostro foglio dati'[Acc max], 
    0.95
)

Step 3 - Determining the thresholds in %

After determining the maximum value on which to calculate the thresholds, we can decide on the different "intensity" categories. In my case, I chose four: Very Low, Low, Moderate, and High, and the "barrier" percentages are 25, 50, and 75% of the maximum.

Now we will see how to calculate them, for simplicity of exposition we will see one but the procedure is the same for all of them, you just need to change the % or the name of the category.

-- Calcolo della soglia al 25% basata sul percentile del 95%
Soglia_25 = 
[Soglia 95 Percentile ACCmax] * 0.25

In this case, the 25% threshold is calculated, below which we find the Very Low category referring to the Acceleration thresholds. The same procedure can be used for decelerations or speeds, and you can choose different percentages based on your needs.

Step 4 - Range Determination

Now, to create a more professional effect by determining the range of the different thresholds where, for example, "< 3 m/s" or "2.6-3.4 m/s" will appear in our table, we need to use the FORMAT function. Still considering our Very Low category, we will create a new measurement like this:

Range_Fascia_Vey Low = 
VAR Rif = [Soglia 95 Percentile ACCmax]
VAR Minimo = [Soglia_25]
RETURN
"< " & FORMAT(Minimo, "0.00")

while in the case of a range between two numbers we will do this (Moderate)

Range_Fascia_Moderate = 
VAR Rif = [Soglia 95 Percentile ACCmax]
VAR Minimo = [Soglia_50]
VAR Massimo = [Soglia_75]
RETURN
FORMAT(Minimo, "0.00") & " - " & FORMAT(Massimo, "0.00")

Step 5 - Creating the table or matrix

After determining all the measurements we needed, we can finally create our table or matrix with the customized thresholds. We'll insert the fields we need, such as Player, Image (if you want to include player heads), and the various customized thresholds you've created.

Now all you have to do is format the table as you like, choosing the title, as well as the colors and fonts that compose it. I personally chose a clear formatting style similar to Manchester City.

If you want to download the complete Power BI Dashboard, use this link 📁 PowerBi Dashboard | Individual Acceleration and Deceleration Thresholds