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.
- 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.
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.
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.
12 thoughts on “2021 Week 2 | Power BI: Creating Basic KPI Report”
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.
It was one of last week’s challenge requirements (remove null values and Conference Median), my numbers match.
My Numbers were basically the same but at the Billion level and not the Million
You can customize the detail level in the visual settings and switch from billions to millions.
Great Challenge. Thank you very much for your effort 🙂
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.
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!
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.
Hi Zineb! Double check that the column of interest is formatted as a number.
While the Https gets renewed, this is the link to the video
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!
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.