cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

New Column For Champion Based on Year

The champion in this case is the 75 Sabres for having attained 4 wins in the Final Round. How do I create a separate column declaring the team with 4 total wins in the final round the champion?

 

Something like... If(Calculate(Sum(Database[Win]), Database[Round] = "final") = 4 for a given year, then Champion, N/A)

 

 

It's how to get that 4 for a given year in there that is given me an issue. Any thoughts? The Chart is below for any reference. 

 

YearGameTeamOpponentWinLossTieStageRound
20056775 Sabres82 Islanders 1 R16Final
20056782 Islanders75 Sabres1  R16Final
20056475 Sabres82 Islanders1  R16Final
20056482 Islanders75 Sabres 1 R16Final
20056375 Sabres82 Islanders 1 R16Final
20056382 Islanders75 Sabres1  R16Final
20056975 Sabres82 Islanders1  R16Final
20056982 Islanders75 Sabres 1 R16Final
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: New Column For Champion Based on Year

@bw70316 try this measure (NHL Fan) 🏒🏒

 

Measure = 
VAR __wins = CALCULATE ( SUM ( Table[Wins] ), ALLEXCEPT ( Table, Table[Team] ), Table[Round] = "Final" )
RETURN
IF ( __wins = 4, "Champion", "N/A" )

 

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: New Column For Champion Based on Year

@bw70316 try this measure (NHL Fan) 🏒🏒

 

Measure = 
VAR __wins = CALCULATE ( SUM ( Table[Wins] ), ALLEXCEPT ( Table, Table[Team] ), Table[Round] = "Final" )
RETURN
IF ( __wins = 4, "Champion", "N/A" )

 

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper V
Helper V

Re: New Column For Champion Based on Year

That solution worked, but I'm trying to have one row for champion for each year. Or I could do something like Final Wins - Final Losses where 

Final Wins = CALCULATE(Sum(Database[Win]), Database[Round] ="final")
and Final Losses =  CALCULATE(Sum(Database[Loss]), Database[Round] ="final")
 
and do something like IF(Final Wins> Finals Losses, "Champion", ) but I still need to break it down by year in some fashion for teams that have more final L's in different years. Thanks for taking time to look at this

 

200613196 Panthers06 France 1 R16FinalO396 Panthers06 FranceN/A 
200612996 Panthers06 France 1 R16FinalO106 France96 PanthersN/A 
200612906 France96 Panthers1  R16FinalO106 France96 PanthersChampion
20056882 Islanders75 Sabres 1 R16FinalO675 Sabres82 IslandersN/A 
20056875 Sabres82 Islanders1  R16FinalO675 Sabres82 IslandersChampion
20056775 Sabres82 Islanders 1 R16FinalO582 Islanders75 SabresChampion
20056782 Islanders75 Sabres1  R16FinalO582 Islanders75 SabresN/A 
20056682 Islanders75 Sabres1  R16FinalO475 Sabres82 IslandersN/A 
Highlighted
Super User IV
Super User IV

Re: New Column For Champion Based on Year

@bw70316 In ALLEXCEPT funtion, add year into it, that will work by year.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper V
Helper V

Re: New Column For Champion Based on Year

champion = VAR __wins = CALCULATE ( SUM ( Database[Win] ), ALLEXCEPT (Database, Database[Team], Database[Year]), Database[Round] = "Final") Return If(__wins = 4, "Champion", "N/A")
 
Do you mean like this above? It yielded the same results in that I have several champions or a N/a for each finals grouping. 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors