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

[Anonymous]

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.

[harshnathani]

Hi @Anonymous ,

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/89664

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)

[kriscoupe · Accepted solution]

Hi @Anonymous,

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

[Anonymous]

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' 

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

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.

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! 

[kriscoupe]

Hi @Anonymous,

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