UPDATE 2015-11-05: Now Read the Solution Post

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.

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

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.

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!

#### Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

1. Sarah E. says:

Jim,
Hi there, I am a interning in Analytics and was examining this as an exercise. I was stumped when relating the Round table to the Judges scoring. I see how you rearranged each judge table so that the relationship would work –
Was that brute force method, or did you use a tool within PowerPivot?
Thanks for posting 🙂

-Sarah

1. @Sarah E.
I imported each Judge table into Power Query and used the “Unpivoted Column” command to narrow each table, then loaded the results into PowerPivot. If you open the Power Query window and click on any of the tables you can see the steps I took.
Not sure if that is cheating or not 🙂

1. Sarah E. says:

@Jim,
I was suspicious that the data needed to be rearranged, however I new that there had to be a “cheating” type method. This sounds great – I will be attempting to avoid looking at the rest of your solution so maybe I can finish it now that I am past this road block. THANKS!!!

🙂
-Sarah

1. John M says:

I am running the 64-bit version of Excel 2013. Should I be able to open the data model for this example? Excel states that the data model was created in a previous version of the PowerPivot add-in. When I hit OK to upgrade the workbook, I receive a message stating that it is unable to upgrade the data model due to an error while upgrading.

1. Avi Singh says:

John, there is no data in the PowerPivot model for the workbook, so not sure why it’s asking for upgrade and then failing.
You can try creating a new Excel 2013 workbook and use this file simply as a Data Source into Power Query. That should work.

2. Alex says:

@Jim you can improve your solution to handle cases where is no score, e.g. on slicer Round select Q&A. Just change formula Rank to =RANKX(ALLSELECTED(Contestant), [Score Weighted])*([Score Weighted]/[Score Weighted]) – the last part handle cases where there is no score

1. Thanks Alex what a simple yet slick way to validate the weighted score.

3. Also facing the problems with the non-editable datamodel, so having an excuse to come up with a PowerQuery-only solution 🙂 : Just one query returns all we need. So no need to push tables to the datamodel, create connections…

There’s also a version that shows 2 ranks: One that responds to the filters and another one returning the overall rank – which could come in handy as well. This would actually have been easier using Power Pivot because currently there is no RANK-function in Power Query and you have to provide for the case where 2 (or more) candidates score equally.

! In order to follow what each step is doing, you need to save the file to a local or network folder (no internet-folder).

https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!16294&parId=DE165DDF5D02DAFF!107&app=Excel

1. Thanks Imke, really like your blog, not sure if it was a language issue, but to get your query to pull for myself (U.S) had to make a quick change to your Cell A1 reference from =CELL(“dateiname”) to =CELL(“filename”) and that seemed to clear up my issue, just in case anyone else had some trouble viewing Power Query.

Thanks for all your hard work and the examples you’ve provided.
Big fan,
Jim

1. Thanks Jim,
wouldn’t have expected that this will not be translated, as it’s part of the standard-formula. But of course, you’re absolutely right. This then would need to be localized into every other different language as well!