Brand Of
Brand Of
Brand Of
The syllabus is organised into a series of modules, each presented as a series of “challenges” – a specific requirement from your stakeholders or a task that you need to complete as part of your reporting solution. Through each challenge, you will have the opportunity to not just learn the features of Power BI, but will also be able to practice with interactive “try it out” exercises, quiz questions and downloadable templates In MS Word/Excel.
Each challenge consists of a number of micro-learning lessons and is expected to take circa 1-2 hours to complete You can come back and complete lessons over time though please ensure you allocate sufficient time to complete the exercises and activities in each lesson.
In the Introduction module we will look at the current situation (building Financial Statements with Excel), the Actuals/Budget dataset and the example solution we will end up with at the end of the course.
Power BI isn’t there to “make data look pretty” but rather to deliver specific insights and answer specific business questions from the intended user base. So the starting point for analytics is to identify the audience, their questions, associated logic and “slice and dice” attributes that are required to deliver those insights. This is a non-technical exercise focused around gathering requirements and conceptually designing a semantic model that will form the foundation of your reporting and modelling solutions.
You will come away from this session with an approach and templates (which you can immediately use for your own use cases!) to capture stakeholder requirements for report and translate these into a data structure (semantic model) required for Power BI.
Our data model design from the previous module has provided a blueprint for how we need to shape our data into the right structure. This is done through a sequence of activities known as j/ Extract, Transform and Load (ETL) – bringing data into Power BI from data source(s), cleaning and shaping that data as required, and loading it into a Power BI data model. In this course our data source is Excel/CSV files with some specific data cleaning activities (e.g. header rows in each of our actuals files, dates potentially in a different format to our machine locale) and transformation activities (e.g. calculating amounts in Reporting currency given local currency amounts/a separate table of exchange rates).
After this module you will know how to ingest data from a Teams Channel (SharePoint) in Power Query and create a sequence of queries to shape and clean data (without writing any code!) and embed logic for currency conversion.
Content is only available in the full course.
Now that we have our data loaded into Power BI, we need to create the table relationships and a set of base measures for Actuals/Budget and key metrics such as Revenue, Gross Profit and Net Margin. We will also leverage time intelligence to show MTD, QTD or YTD values depending on a slicer selection.
After this module you will know how to write measures in DAX, including understand the power of time intelligence to create dynamic calculations
Content is only available in the full course.
Data visualisation is all about drawing the attention of your audience to what is important – providing the right context to the numbers so that they can quickly evaluate performance and what is driving it. Variance analysis – e.g. highlighting and explaining variances from actuals to budget or Prior year - is the cornerstone of understanding financial performance and becomes very powerful in Power BI through visualisations that enable “slice and dice” capability.
Content is only available in the full course.
Here we will build financial ratios (e.g. Cost:Income ratio) and the measures we need for an Income Statement in Power BI using the default matrix visual. This will leverage our Income Statement Rows “helper” table to define the layout, and a dynamic calculation in DAX to calculate either total Actuals/Budget (based on our relationships via the Chart of Accounts), Subtotals (E.g. Gross/Net Profit) or custom calculations (e.g. Margins). We will also look at dynamically formatting the calculations – e.g. depending on whether it is a percentage or currency value, and whether the Income Statement is showing consolidated values across multiple organisation units (in which case it will show values in reporting currency) or a single organisational unit (with values formatted according to the local currency e.g. £ or €).
After this session you will know how to use a helper table to define the layout(s) required for the Income Statement, build a chain of measures to dynamically show the right totals/subtotals/calculations and format the measures with a dynamic format string.
Content is only available in the full course.
Building Interactivity into a Power BI report is what really allows your stakeholders to “self-serve” on the data – e.g. honing in on areas of interest by drilling through from a line on the Income Statement to the underlying journals/transactions that make it up, or being able to hover over a point on the Income statement to see the breakdown throughout the month using report page tooltips.
After this session the Income Statement matrix will be in a fixed format with the right hierarchy, and will leverage some of the Power BI interactivity features to allow for exploratory analysis.
Content is only available in the full course.
The default visualisations built in the first course are a good starting point, but for financial analytics we may need to customise these, consider other visual types or use custom visuals from AppSource in order to highlight what we want. For example, if we want our waterfall chart to just show the difference between two points (e.g. current/prior month values) broken down by an attribute, in our case we will have to use a specific DAX measure and disconnected table to enable this. We will also look at the decomposition tree visual (with a dynamic measure selected using field parameters) and Zebra BI custom visuals which is excellent at highlighting variances.
After this module you will learn some advanced visualisation techniques to enhance the storytelling capabilities of your Power BI report.
Content is only available in the full course.
In this module, we will look holistically at the Power BI environment and think about how we should organise our workspaces and the objects within them. We will also consider how the data assets that we have built can be re-used for other reports, as well as managing security and access.
Content is only available in the full course.
