cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sach
Helper III
Helper III

Get totals based on two columns

I'm building a Power BI dashboard for ATP tennis stats. I have raw data in CSV format, which as a ton of fields, but narrowed down to relevant stuff for this question, it looks like this. I have full stats for all ATP games, but I narrowed down to Grand Slam finals in the last few years in order to give you a smaller sample data set.

 

 

Year,Tournament,Winner,Loser
2017,Australian Open,Roger Federer,Rafael Nadal
2017,Roland Garros,Rafael Nadal,Stan Wawrinka
2017,US Open,Rafael Nadal,Kevin Anderson
2017,Wimbledon,Roger Federer,Marin Cilic
2018,Australian Open,Roger Federer,Marin Cilic
2018,Roland Garros,Rafael Nadal,Dominic Thiem
2018,US Open,Novak Djokovic,Juan Martin del Potro
2018,Wimbledon,Novak Djokovic,Kevin Anderson
2019,Australian Open,Novak Djokovic,Rafael Nadal
2019,Roland Garros,Rafael Nadal,Dominic Thiem
2019,US Open,Rafael Nadal,Daniil Medvedev
2019,Wimbledon,Novak Djokovic,Roger Federer
2020,Australian Open,Novak Djokovic,Dominic Thiem
2020,Roland Garros,Rafael Nadal,Novak Djokovic
2020,US Open,Dominic Thiem,Alexander Zverev
2021,Australian Open,Novak Djokovic,Daniil Medvedev

 

 

This is all good, and if I want to get the number of wins by player etc, I could easily do that using COUNT of winner column. But I'd like to do more, such as total number of matches played for each player, or win percentage for each player, to name a few. In the above data set, if you take Dominic Thiem for instance, he's played in 4 slam finales and won 1 for a win percentage of 25%. 

 

With a data set arranged like this, how do I create a table, for instance that has columns [ Player, Matches Played, Wins, Losses, Win % ]? Do I need to create a new column for Player and double the data set size? I tried using Measures but not quite sure how to go about it.

1 ACCEPTED SOLUTION
sayaliredij
Solution Supplier
Solution Supplier

You can try following steps

 

1. Import the csv in power BI

2. Go to the transform Data (power Query)

3. Select Columns - Winner and Loser

4. Click on Unpivot columns

5. You will see the following results

sayaliredij_0-1617825561197.png

 

6. Close and apply

7. Create new calculated column 

 

WinningValue = IF(test[Attribute] = "Winner",1,0)
8. Create New Measures = 
WinPercent = SUM(test[WinningValue])/COUNTROWS(test)
 
Regards,
Sayali
 
If this post helps, then please consider Accept it as the solution to help others find it more quickly.

 

View solution in original post

2 REPLIES 2
sayaliredij
Solution Supplier
Solution Supplier

You can try following steps

 

1. Import the csv in power BI

2. Go to the transform Data (power Query)

3. Select Columns - Winner and Loser

4. Click on Unpivot columns

5. You will see the following results

sayaliredij_0-1617825561197.png

 

6. Close and apply

7. Create new calculated column 

 

WinningValue = IF(test[Attribute] = "Winner",1,0)
8. Create New Measures = 
WinPercent = SUM(test[WinningValue])/COUNTROWS(test)
 
Regards,
Sayali
 
If this post helps, then please consider Accept it as the solution to help others find it more quickly.

 

View solution in original post

Yes, this worked, thanks!

I wanted the original Winner and Loser columns as well so I used two duplicated columns for unpivoting. And I also created an Opponent column as well. Between all this it should give me all I want.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.