KPI visualization from excel file

[texnoi]

Dear all,
I am trying to create a simple training dashboarboard.

So far it looks somehow working, but i fail to update the KPI visuals to work properly. 

This is how my database looks like in Excel (user hides the days when he did the training, but database is kept intact).

This is how the target for the KPI visuals looks like (same database file, different tab):

What i am failing to do is make the dashboard show the correct progression in the KPI visual.
If i put one item as manual measurement it works, but i need 47 measurements so that when i pick an item (exercise) its hows how far or after the KPI the user currently is.

Attached are my files:
- Excel database: https://pixeldrain.com/u/rNUMkwzo
- PowerBi Dashboard: https://pixeldrain.com/u/cs9RUvcj

Thank you very much for your help in advance for any tips!
Warm regards,
Texnoi

[AmiraBedh]

I am a little bit lost here ? Which visual is having the issue ?

[texnoi]

Dear AmiraBedh,
The visual i am strugling with is the KPI Visual.

What i am trying to do is to have a dynamic visual that changes KPI based on chosen variable (in my case table showing different exercises).

So when i add a filter (Show me all entries from August 2023) and compare with the target (90) i am not getting the correct visual.

Target is 90. Reached is 70. KPI Visual shows this:

Thank you very much for your help!
Warm regards,
Texnoi

[AmiraBedh]

I can't find any visual with the one you are sharing :

What i am trying to do is to have a dynamic visual that changes KPI based on chosen variable (in my case table showing different exercises)

Can you provide an example of the 2 selections and the KPIs you want to show ?

[texnoi]

It is the small KPI visual. It must show the same as the table on the left.

For example: Target for Box Squat is 100. It should be showed in the KPI visual when the exercise picked from the filter to the right.

[AmiraBedh · Accepted solution]

I created two measures :

Dynamic Average = 
SWITCH(
    SELECTEDVALUE('Data v2'[Exercise]),
    "Box Squat", CALCULATE(AVERAGE('Data v2'[Reps3]),'Data v2'[Exercise]="Box Squat"),
     "BG Squat", CALCULATE(AVERAGE('Data v2'[Reps3]),'Data v2'[Exercise]="BG Squat"),
    BLANK()
)

This measure will calculate the average based on the selection. You can populate it with all possibilities.

For the target I followed the same logic :

Dynamic Target = 
SWITCH(
    SELECTEDVALUE('Exercises / Target'[Exercises]),
    "Box Squat", 100,
     "BG Squat", 30,
    BLANK()
)

[texnoi]

Thank you it did help! What if i want to combine values from 3 databases:

For example just for the box squat: I want to combine Reps1+reps2+Reps3+Reps4+Reps5 so that i use their data as well. Is it still the Dynamic Range command i need? 

Dynamic Average = 
SWITCH(
    SELECTEDVALUE('Data v2'[Exercise]),
"Box Squat", CALCULATE(AVERAGE('Data v2'[Reps1]),'Data v2'[Exercise]="Box Squat")
    "Box Squat", CALCULATE(AVERAGE('Data v2'[Reps2]),'Data v2'[Exercise]="Box Squat")
      "Box Squat", CALCULATE(AVERAGE('Data v2'[Reps3]),'Data v2'[Exercise]="Box Squat"),
        "Box Squat", CALCULATE(AVERAGE('Data v2'[Reps4]),'Data v2'[Exercise]="Box Squat"),
    BLANK()
)

[AmiraBedh]

Please provide more details about your model so we can help you 🙂