Expanded Viewing – WW-02-07

by

Week 2 of 3 in the report building phase. One thing that is often not fully appreciated is how much reports design and development can become a sinkhole for time (and money). Locking down your plans before you start goes a long way and is why we recommend determining the general layout and requirements first, but even with that in mind, the realities will always stretch things out.

YouTube player

The crux of this report pack is that it has to present meaningful stats content in as simple a way as possible; this means focusing on left click only, significant navigation elements, drill through, and tooltips. Overall there will be three main pages (possibly four if “Latest Results” cuts).

  1. Summary
    • Main page
    • Filter selection (will interact with drillthroughs and tooltips)
    • Jesmond Dene Summary stats (all finishes, all registered finishes, unique runners, average Jesmond Dene parkruns)
    • Event Summary Information (tooltip)
  2. Event Details
    • Drill through page (accessed from chart on main page)
    • Age Category performance
    • Gender Performance
    • Finishers Details (tooltop) – link to parkruners profile
  3. Participants
    • Split of participants
    • Participant Categorisation

With Power BI, you must move away from the mindset of “One Report to rule them all” 20+ page reports take vast amounts of time to set up and prepare and will invariably have significant “momentum” with the minimal business return. Use Secondary (Thin) Reports. When you begin by thinking that the Dataset rules them all, you then plug and play short reports targetted at specific groups. For our three-page report, we may spin the “Participants” page into a separate report in the corporate sense. The needs may be different, so we may not want everyone involved in deciding on look and feel. Many small reports will give you a better result (remember they all point to the same data!)

The tools within Power BI to use Bookmarks, buttons, tooltips, and drill throughs make everything simpler. In this video, we show you how we set up a slicer panel for the next generation of our Jesmond Dene parkrun report, and we will be using button navigation options to support what we are doing.

Dax

Average Age Grade = //Determine the average Age Grade (this will work across multiple events or a single)
AVERAGE( Participation[Age Grade] )
EventTT_Title = // Before text box 2.0 you had to write measures to build up text strings
VAR Event = Max ( Events[Event] ) 
VAR Finishers = Max ( Events[Finishers] )
VAR EventDate = FORMAT ( MAX ( Events[EventDate] ), "dd-MMM-yyyy")
RETURN 
Event & " - " & Finishers & " - " & EventDate
Overall Average Age Grade % = //Produces an average of the Average Age Grades, we prefer this to a true average, either are an option though - see Type 2 below
AVERAGEX ( Allselected(Events[Event]), 'DAX'[Average Age Grade] )
Overall Average Age Grade % - type 2 = 
CALCULATE('DAX'[Average Age Grade], REMOVEFILTERS(Events) )
EventState = //Define a measure that has colours built in
VAR AllEvents=[Overall Average Age Grade %]
VAR Event = 'DAX'[Average AG]
RETURN
SWITCH(TRUE(),
        Event < AllEvents,
        "LightSlateGrey",
        Event > AllEvents,
        "SteelBlue"
)

Leave a Reply