One of my customers runs interactive courses for Virtual Staff Engagement and wanted to explore the idea of a virtual leader board which refreshes from a Google sheet and ranks participants based on their score of the game and when they participated in the game using PowerBI. The leader board should be nice and simple and easy to use without any instruction.
This required extracting data from Google Sheets which I hadn’t need to do before, but if I wanted to do it, chances are someone else would have wanted to. Having a variety of clients, with many different needs means you need to be good at research and finding out how to do what you need to do. Fortunately, I have been doing this for a while and know which resources are good to use.
Curbal is one of those and is frequently one of those! If she is posting something it will work. To pull in data from Google Sheets you need to change the url from Google sheets and edit the end to export to excel rather than edit. Look at the clip which shows you how to do this. Also, Ruth has written a function which makes it useable for any Google sheet which gives the ability to replicate the process of data extraction for Google sheets.
Next step, transform the data from the Google sheets into a nice tidy data set that will allow me to model it appropriately. This I do using Power Query with the basic aim to have one table that has a column for every type / attribute of your data and each row holds the unique value, eg, each test or transaction. In data modelling terms this is a fact table and gives you information about the process that has happened. Each column needs to be the same type of data, eg, for date it must only include date fields.
Once I have the data in this process, I can then model and perform business logic required. This is where DAX comes in and in particular https://www.daxpatterns.com/ 😊
What are the requirements?
- The customer wants to show a % score per team
- A ranking per team for the day that they took the test
- An overall ranking for every team
- The current leader
- To be able to filter by day
- One page and simple
For this I created measures to find:
- the % Score
- the Max Score
- the Overall Rank
- the Rank in a Day
- a Hierarchy Level
- a % measure to use the Hierarchy Level to make the visual clear
A Calculated table to show only the top score.
Once the data model has been built, it is essential to ensure that your report page is clean and easy to use so that when a user engages with it, they can understand the information visualised and the report is clear to use. I’m not a designer but I ensure that I use the Format button to align all of the elements, I group elements together and follow the eyes natural path from top left in a Z format to the bottom right. Creating a visually appealing and clear report is a science in its own right and needs to considered when designing a report, but that is the subject of another post!