UPDATE 2015-11-05: Now Read the Solution Post
I have worked with NASA, NBC Universal and many other top companies since joining P3 Adaptive . But Power Pivot and Power BI work equally great (if not better) at small businesses. In fact, even at large companies, we typically engage with small business teams.
This is how I found myself, building a Power BI solution for Miss India beauty pageant in Washington USA, organized by Ravishing Women.
Power Pivot was there!
Click to meet all the pageant contestants
(p.s. This event was in May 2015, but blogging about it now. Yup, that’s how busy this year has been)
I could show you the solution, instead I will turn this into an exercise for beginners.
Exercise for Beginners
Read the scenario below and use the provided files to work out a solution for yourself. Use the tool of your choice: – Power Pivot & Power Query for Excel 2010/2013 or Power BI Desktop. I will present the solution I created, in a follow-up post.
Note: All data has been anonymized and randomized. Scenarios have also been simplified for this training exercise.
Scenario
Organizers of the show have approached you, and described the difficulties they faced in last year’s contest. Scoring was done manually, which caused significant delays and also ran the risk of manual errors. For this year’s show, they would like a system that can let them:-
- Easily input scores as entered by judges
- Calculate weighted scores and rankings quickly and accurately based on multiple criteria
Desired Result: Here is the end result that we would like to achieve. A pivot table as shown below, with
- Measures = ScoreTotal, ScoreWeighted, Rank
- Slicers = Category (Miss, Mrs), Day (Day1, Day2), Round, ScoreType
This would allow organizers to determine
– Which contestants advance to the final round
– Overall winners after the final round
– Specific awards, like Ms. Talent (based on the scores in the Talent Round)
After some discussion with the organizers, you have come up with the data tables as shown in the section below.
(Beginners: Note that BI can be art+science. Gathering requirements as above and translating to a data model falls on the art side, and is a useful skill which you’ll hone with practice)
Data
Click to Download the file with all tables.
Judges: We have six judges as below
Contestants: We have two groups of contestants, for the Miss and Mrs. category. Each contestant is identified via a number, which is pinned to their dress and would be used by judges for scoring.
Rounds: We have multiple rounds on day one and date two. Each round receives separate scores for presentation and performance. Top three contestants from each category would go on to the Q&A round, where the final winners would be decided.
Scoring sheets: Judges enter the scores on paper scoring sheets, which would need to be collected and quickly entered into our data model.
Click to Download the file with all tables.
Advanced
What other insights can you gain from the data or how else could you augment the model? (WARNING: Sample data). That’s the part I love about data. You can explore in any direction you want. My thoughts were around excluding the highest and lowest score (I think they do that in the olympics) to account for bias.
Have at it! I would post the solution in a followup post, so you would be able to compare your solution with mine (there is always more than one way to solve a problem). If you like, you can upload your solution to OneDrive etc. and post a link in the comments below. I would not be able to validate, but others may find it useful to see the different approaches taken to solve this scenario.
Me and my daughter (the lil’ one) with some of the winners. Click to watch more pics from the event
Power On!