2021 Week 2 | Power BI: Creating Basic KPI Report

Introduction

Thanks to everyone who participated in Week 1 of #WorkoutWednesday – Power BI edition. If you are joining this week, you may want to revisit the first week’s challenge as this week will build on the data model that we created in W01. We also have the PBIX file available here. This week we are going to be creating a basic KPI report. We will be incorporating important tenants of KPI reporting such as BANs, trends, and breakdowns. 

Requirements

  • Create a new field in Power Query (using M) called Total Profits. This field will be calculated by subtracting Total Expenses from Total Revenues and then adding back in Excess Transfers Back.
    • The background on this is that in the past few years, the NCAA has allowed schools to exclude money that was transferred back to the main institution (from the athletic department) from showing as profits. By adding back in Excess Transfers Back we are finding the total amount of money generated by the different athletic deparments
    • Definition: Positive net revenues generated by athletics and transferred to the institution for non-athletics purposes. These funds are in excess of the transfers subtracted from the institutional and governmental funds allocated to athletics. (from http://cafidatabase.knightcommission.org/about-the-data)
  • Create KPI dashboard that includes 3 high level measures: Total Revenues, Total Expenses, and Total Profits. 
  • Must include a trend line for each category and breakdown by conference.
  • Indicate that this view is the Summary level view within the header area. 
  • Colors and design of the header section is at your discretion.

Dataset

This week uses a data set that breaks down NCAA athletic department expenses and revenues by year. You can get it here at data.world. This is the same data set that was used in W01. 

Share

After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year. 

Solution

21 thoughts on “2021 Week 2 | Power BI: Creating Basic KPI Report”

  1. Keep in mind that your numbers will differ slightly from what is shown because it appears that conference medium was removed from the NCAA Division after the video / files were created.

  2. Hey, out of interest what are the advantages of adding the Total column in the power query rather than adding a new column to the table? Hopefully that make sense, I have been doing quite a bit of data visualisation but very new to the manipulation side of things.

    1. Although this use case may not be the most efficient (as you could have created a calculated measure instead), there are still plenty of use cases where doing your row level logic in the data model layer as opposed to a calculated column can increase your performance. Hope that helps!

  3. Hello, I need some help please while creating the new column “Total Profit” i have an issue concerning the agregation, I only get “Number”, “first one” and the “last one” which means i don’t get the sum nor the medium and obviously I need the sum.

  4. Trying to learn power bi, just did this one and am looking forward to going through the series. The videos are great to explain how the reports are generated. I can’t publish as I am not using a work or school account, but mine looks like it should!

    1. Shannon Lindsay

      We’re so glad you’re participating! In the future, you can use the M365 developer program to get a free Power BI license for learning! Check out how to sign up in our blog post.

    1. Hi Selva,

      Your numbers will differ slightly from what is shown because it appears that conference medium was removed from the NCAA Division after the video / files were created.

    1. Hi J – total profits appears to be revenue minus expenses. You can create this using a measure. I hope this helps!

  5. Pingback: Week 02 #WorkoutWednesday Power BI challenge – KiwiMaori

  6. My numbers for the KPI’s are different. Am I doing something wrong here? Spencer…or anyone else.
    Total Revenues — 287.36 Million
    Total Expenses — 217.36 Million
    Total Profit — 191.76
    Appreciate your response. Thanks

    1. The data hasn’t changed, so I think your numbers should match. Make sure you don’t have any filters or cross-filters applied.

  7. Enjoying learning power BI here.
    I ran through the scenario twice to make sure I understood and caught the nuance of showing labels and removing the x-axis after the second time.
    Thank you Rob

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top