Power BI at Beauty Pageant: An Exercise for Beginners

I have worked with NASA, NBC Universal and many other top companies since joining PowerPivotPro. 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.

20151022 PowerPivot at Beauty Pageant Power BI at Beauty Pageant: An Exercise for Beginners
Power Pivot was there!

20151022 Beauty Pageant Contestants Power BI at Beauty Pageant: An Exercise for Beginners
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)

image thumb4 Power BI at Beauty Pageant: An Exercise for Beginners

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
image thumb1 Power BI at Beauty Pageant: An Exercise for Beginners

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.
image thumb2 Power BI at Beauty Pageant: An Exercise for Beginners

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.
image thumb3 Power BI at Beauty Pageant: An Exercise for Beginners

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.


20151022 Beauty Pageant Me Daughter Power BI at Beauty Pageant: An Exercise for Beginners

Me and my daughter (right) with some of the winners. Click to watch more pics from the event

Power On!

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

PowerPivotPro