cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Defining a "Winner" from Election results over State and Year

Hello

 

I am new to power BI and a beginner with DAX. Apologies if this post is in the wrong location.

 

I have USA presidential election data from 1976 onwards. Each row shows the state, the year, the candidate, the party, and the amount of votes the candidate recieved.

 

E.g.

Alabama, 1984, Ronald Regan, Republican, 872849,

Alabama, 1984, Mondale Walter, Democrat, 551899

Alabama, 1984, Other candidates, Other, [sum of votes of Other Candidates]

 

I would now like to create a column (or measure..?) showing which party won in each State and in each year (answer for table above is Republican).

 

I suspect this isn't too difficult to do but my own shortcomings are letting me down!

 

Any help is greatly appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: Defining a "Winner" from Election results over State and Year

Hi @Staja809,

 

You can create a new measure like this

 

 

Winning Party = 
VAR MaxVotes = MAX('Table'[Number of Votes])
VAR TopCandidate =
CALCULATE(
    SELECTEDVALUE( 'Table'[Party] ),
    'Table'[Number of Votes] = MaxVotes
)
RETURN
IF(
    HASONEVALUE( 'Table'[State] ) && HASONEVALUE( 'Table'[Year] ),
    TopCandidate
)

 

 

Then build a matrix with State and Year on the Axis and you should have your winning party.

 

Let me know if this helps. If this solves your problem, please mark this answer as a solution and drop a kudos 😉.

 

Thanks

Kris

View solution in original post

4 REPLIES 4
Highlighted
Resolver IV
Resolver IV

Re: Defining a "Winner" from Election results over State and Year

Hi @Staja809,

 

You can create a new measure like this

 

 

Winning Party = 
VAR MaxVotes = MAX('Table'[Number of Votes])
VAR TopCandidate =
CALCULATE(
    SELECTEDVALUE( 'Table'[Party] ),
    'Table'[Number of Votes] = MaxVotes
)
RETURN
IF(
    HASONEVALUE( 'Table'[State] ) && HASONEVALUE( 'Table'[Year] ),
    TopCandidate
)

 

 

Then build a matrix with State and Year on the Axis and you should have your winning party.

 

Let me know if this helps. If this solves your problem, please mark this answer as a solution and drop a kudos 😉.

 

Thanks

Kris

View solution in original post

Highlighted
Super User V
Super User V

Re: Defining a "Winner" from Election results over State and Year

Hi @Staja809 ,

 

 

You may want to have a look at this in the Community gallery.

 

https://community.powerbi.com/t5/Data-Stories-Gallery/2016-Presidential-Election-Polls-Analysis/td-p...

 

The .pbix file is attached there too.

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted
Regular Visitor

Re: Defining a "Winner" from Election results over State and Year

Hi @kriscoupe  

 

Thanks for your help - I suspect you have the correct answer but (most likely due to my own lack of expertise) I have still failed to get the answer I wanted. 

 

Here is a snapshot from my table 'Election_Results' 

Staja809_0-1593507694010.png

 

And here is a snapshot of your formula with my variables incorporated. 

 

Staja809_1-1593507912415.png

 

However when I try to input Winning Party into a matrix it shows no result and I can't get a result with any other displays either.

 

Staja809_3-1593508334563.png

 

 

Now at this point you are probably shaking your head in disbelief at my ineptness with Power BI and DAX and tempted to give up on me, which I understand. However, if you see how the problem could be quickly fixed then let me know!

 

Thanks again for all your help! 

 

 

Highlighted
Resolver IV
Resolver IV

Re: Defining a "Winner" from Election results over State and Year

Hi @Staja809,

 

Ah no sorry it seems I may have typo'd not thinking how your matrix would be set up. So the HASONEVALUE formula should be

 

IF(
    HASONEVALUE( Election_Results[State] ) && HASONEVALUE( Election_Results[Year] ),
    TopCandidate
)

 

Basically the HASONEVALUE makes sure that you have a state/year in the context of the measure. Otherwise if you have subtotals in the matrix you will get party names in the subtotals such as who had the highest voting total for a state in 2014. If you remove the subtotals from the matrix then you can actually RETURN TopCandidate without the IF I believe.

 

Let me know how you get on. I'll amend my original answer to include this new method so the answer is not split 🙂

 

Kris

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors