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

Rank Choice Voting formula

We are having an election with rank choice voting. Voters can choose their 1st, 2nd, 3rd, 4th choice of candidates. There is only one winner. The winner is the first person to get the majority vote. At each round of calculation, we remove the lowest ranked candidate then look at the remaining options.

 

I have a table with three columns: VoterID, Candidate, Rank

I made a measure to count all the votes: Votes = Countrows(VoterTable)

 

To count the vote, we start by looking at everyone's 1st choice: 

 

Round1 1st Choice = CALCULATE([Votes],VoteTable[Rank]=1)

So far so good.  

 

But here is where I am stuck:

If no candidate gets the majority in the first round, then we remove the lowest ranked candidate and look at the 2nd choice FOR THOSE WHO VOTED for that, now removed, candidate. So I need to find the VoterID of those who voted for the last ranked candidate and look at their Second choice. Then I need to count the FIRST choice of all the successful candidate voters and the SECOND choice of all the people who's first pick has been eliminated.

 

Any suggestions?

 

Here is a sample data table

VoterID Candidate Rank
0 Donald Duck 2
0 Minnie Mouse 3
0 Mickie Mouse 4
0 Dolly Duck 1
1 Donald Duck 1
1 Minnie Mouse 4
1 Mickie Mouse 2
1 Dolly Duck 3
2 Donald Duck 2
2 Dolly Duck 1
3 Minnie Mouse 2
3 Mickie Mouse 1
4 Donald Duck 2
4 Minnie Mouse 3
4 Mickie Mouse 4
4 Dolly Duck 1
5 Donald Duck 2
5 Dolly Duck 1
6 Donald Duck 1
6 Minnie Mouse 4
6 Mickie Mouse 3
6 Dolly Duck 2
7 Donald Duck 2
7 Minnie Mouse 3
7 Mickie Mouse 1
7 Dolly Duck 4
8 Donald Duck 1
8 Minnie Mouse 3
8 Mickie Mouse 4
8 Dolly Duck 2



2 REPLIES 2
Highlighted
Microsoft
Microsoft

Hi @kmclorg,

 

I am not sure how you want to analyse data in a visual, based on my assumption, I created a measure [Round2 2nd Choice] and show it ina clustered column chart together with [Round1 1st Choice].

lowest ranked = CALCULATE(MINX(VoterTable,[Round1 1st Choice])) 

Round2 2nd Choice = IF([Round1 1st Choice]=[lowest ranked],BLANK(), CALCULATE([Votes],VoterTable[Rank]=2))

1.PNG

 

From above chart, we can Intuitively find the winner via checking the FIRST choice and SECOND choice.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Thank you very much for this. It is helpful but derives the wrong answer (my fault - because I did not provide you with the details about how the vote is counted).

 

After posting the question, I realized that I needed to learn the precise name and details of our particular voting system. We are using "Standard Preferential System" which basically, looks at the first choice of each voter but never looks at second or subsequent choices on any ballot unless a voter's first choice candidate has been eliminated from the race. I found an explanation of all the different ways of counting rank ballot systems in this link:  https://www.aec.gov.au/About_AEC/AEC_Services/Industrial_Elections/voting.htm#four

 

 

Using Standard Preferential system with my data set, results in a win for Dolly Duck not Donald (strange but true).

 

I never did figure out a DAX formula for this because I am pretty weak with DAX, but I did manage to use Power Query to create the data sets to chart. This is NOT a real solution because I had to manually type in the names of each losing candidate in order to get my results (not worth figuring out how to do it properly as we are only running the vote one time and the polling company will give me this information - my mission was simply to chart it).

 

Here is a link to how I have depicted the results in PowerBI: 

 

https://1drv.ms/f/s!AslX_Cn_svS_gRIeLsmR0OHSF0dM 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors