Get totals based on two columns

[Anonymous]

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.

[sayaliredij · Accepted solution]

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

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.

[Anonymous]

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.