Forum Discussion
Rank Choice Voting formula
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
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: