Rank Choice Voting formula

[kmclorg]

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

[v-yulgu-msft]

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))

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

Regards,

Yuliana Gu

[kmclorg]

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