cancel
Showing results for
Did you mean:
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

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

Hi @Staja809,

You can create a new measure like this

``````Winning Party =
VAR TopCandidate =
CALCULATE(
SELECTEDVALUE( 'Table'[Party] ),
)
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

4 REPLIES 4
Highlighted
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 TopCandidate =
CALCULATE(
SELECTEDVALUE( 'Table'[Party] ),
)
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

Highlighted
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

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!

Highlighted
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