Create a game leaderboard with individual game details

[Lucian]

Hi,

I'm trying to help a friend that need to create some report for a bowling tournment. Until here it seems preety fun. 🙂

Each player will have to play 6 games (labeled as G1...G6) and the data is recorded in a simple table:

Player Name	Game Label	Score
PL1	G1	183
PL1	G2	213
PL1	G3	199
PL2	G1	225
PL2	G2	171
PL2	G3	175
PL3	G1	196
PL3	G2	223

But the final result report should look like this:

Player Name	G1	G2	G3	Game Count	Total Score	AVG Score
PL1	183	213	199	3	595	198.33
PL2	225	171	175	3	571	190.33
PL3	196	223		2	419	209.50

Without Game Count and AVG Score it would be just a plain and simple matrix... but I have no clue how could I have the above picture into a single visual.

Is it possible to obtain it?

Kind Regards,

Lucian

[parry2k]

@tackytechtom it is a great solution but unfortunately not scalable, if tomorrow there is another Game (G4 and G5), it means you have to come back to the measure and include the logic for the new games. Just my 2 cents.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[tackytechtom · Accepted solution]

Hi @Lucian ,

I have a solution here (I used a normal table visual):

First I pivot the table in Power Query on the column Game Label and Values column Score:

After that, I created the measures:

Game Count = 
IF ( ISBLANK ( MAX ( Table16[G1] ) ), 0, 1 ) +
IF ( ISBLANK ( MAX ( Table16[G2] ) ), 0, 1 ) +
IF ( ISBLANK ( MAX ( Table16[G3] ) ), 0, 1 )

Total Score = 
SUMX ( Table16, Table16[G1] + Table16[G2] + Table16[G3] )

Average Score = 
DIVIDE ( [Total Score], [Game Count] )

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Lucian]

Hi @tackytechtom ,

Even is not as flexible I would like to be... is still better than nothing, because I have to follow that report format.

I think I will use this approach until I could find a better one. 😉

@parry2k if you could point me out to some articles on how to use your workaround, it might help, because the real project is based on 6 or 7 games. I have to check if this limits will be increased over time.

Kind Regards,

Lucian

[parry2k]

@Lucian It is a great question and unfortunately there is no functionality in Matrix visual to show what some of the calculations only show at the total level. There are lots of work around using disconnected tables but nothing out of the box.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.