Save content
Have you found this content useful? Use the button above to save it to your profile.
image of question marks | accountingweb | ABC of Power BI: ‘Q’ is for query
iStock_olemedia_question_bulbs

ABC of Power BI: ‘Q’ is for query

by

Continuing his Power BI series, Hugh Johnson describes queries and their distinct roles.

3rd Jul 2024
Save content
Have you found this content useful? Use the button above to save it to your profile.

A query is simply something that selects data and/or performs some kind of action on it. This can also include creating data based on a set of input rules. 

image of income_statement | accountingweb
income_statement

The image above shows the first few lines of an income statement in my profit and loss template. There are 21 queries in the Power BI report dataset that work together to display this matrix. Each one has its own distinct purpose, and they are designed to combine a great user experience with ongoing report maintainability and flexibility. I will describe these queries and their role. I have also prepared an accompanying video The anatomy of an income statement matrix in Power BI.

Trade-off between maintainability and performance

The template has a dataset of a little under 50,000 transactions. Organising the queries in the way outlined in this article produces a report that is very flexible and maintainable. Generally, in a situation where measures call other measures (as we have here), performance of the report will drop. Combining the layers of the measures will in many cases improve performance. 

As a development approach, I would start by separating the measures along the lines that I have done in this template. It is easier to develop and test the report in this way. If performance is an issue, then I would recombine the layers of measures, testing as I go using the performance analyser.

The 21 Queries

image of dataset_queries | accountingweb
dataset_queries

In the image above, I have grouped the queries into four colours to separate the data tables, chart layouts, slicers and measures. We will go through them in that order.

Data tables

The three main data tables are Transactions, Accounts and Dates. The relationship between these three tables is shown below.

image of transactions_dates_accounts_relationships | accountingweb
transactions_dates_accounts_relationships

Transactions (1)

Theoretically, you could calculate a profit and loss (P&L) amount just by using Transactions, and manually applying filters to the transaction date and account values, plus a filter to exclude year-end transfers to the balance sheet. It can be, in fact, a very useful thing to do to support testing of the final report. This table is the single most important table in the report.

Dates (2)

Adding a Dates table with an active relationship between Dates[Date] and Transactions[Date] enables simplified filtering of Transactions by period such as fiscal year, quarter, month, week and so on. Properly set up, it also allows you to take advantage of the Power BI Time Intelligence functions should you wish to. For reasons that go beyond the scope of this article, I choose not to use Time Intelligence functions in my reports. In the template, Dates is generated using Power Query.

Accounts (3)

The Accounts table is a listing of all your general ledger accounts (or at least all the P&L accounts if you are just building an income statement). In the template, Accounts includes information about the hierarchy of the chart of accounts. An active relationship from Accounts[AccountRef] to Transactions[Account], will enable a rudimentary presentation of an income statement relatively easily. 

However, just with these three tables, you cannot achieve the row layout of the income statement shown at the top of this report. The most fundamental problem is the Gross Profit line, which introduces a many-to-many relationship between Accounts and Transactions.

CoA Bridge (called BridgeChartPLAccounts in the template) (3)

The final table that I have classified as one of the data tables is BridgeChartPLAccounts that maps each row in the Income Statement matrix to Accounts. We use this to resolve the many-to-many problem of the gross profit line, and is described in more detail here.

Chart layouts (4)

In the report, I have two tables to control the layout of the P&L matrix; one for the rows (called ChartPL), and one for the columns (called ChartPLMatrixColumns).

Rows (ChartPL)

This table defines each row of the income statement, including blank rows, formatting information and the sort-order for the rows. It is here that I would, for example, specify that a row should be presented as a % (such as gross margin %), or whether the sign should be switched so that expenses are displayed as positive values.

Columns (ChartPLMatrixColumns)

I use a separate table to provide the possible columns that may appear in the matrix. The main reason that I use this table is so that I can have dynamic columns that adjust based on slicer selections (for example if you wish to switch between comparing against the previous year, budget, forecast and so on). It also makes banded conditional formatting, like that shown, a little simpler to implement. 

Slicers

Dates & Period Slicers (DatesSlicers & SwitchPeriod) (5)

Rather than use the Dates table directly in slicers, I always use a separate DatesSlicers table that is disconnected (so has no active relationships with the other tables). This separation prevents the slicer from directly interacting with the report, giving it the sole purpose of capturing the intent of the user. Combined with the second slicer SwitchPeriod, in the example below, we can define the date range we want in three intuitive clicks. In this case, YTD to Febuary 2024. I then use dates measures to interpret this selection (more later).

image of monthly_income_statement | accountingweb
monthly_income_statement

Calculation slicers (6)

Calculation slicers are disconnected tables that drive the selection of different calculation options. Two calculation slicers affect the P&L matrix at the top of this article; one to select whether we are comparing against the previous year or a budget (SwitchComparison), and the other to select whether to show this comparison in absolute terms, or as a % of sales (SwitchCalculation).

In the monthly chart, above, there is an additional calculation slicer (SwitchAggregation) to select whether to show the cumulative total or the monthly figure in each column.

Measures

DAX measures are the final queries that influence what is displayed in the P&L matrix. To support good report maintainability, there are a total of 13 measures driving what we can see in the P&L matrix. Each measure serves a different purpose, and I have split them into four main groups: Base Measures, Dates Measures, Calculation Measures and Presentation Measures.

Base Measures (7)

Base Measures are the only measures that interact directly with the accounting data tables and provide simple aggregations based only on the relationships of these tables. Base Measures are in turn re-used by other measures. 

Using Base Measures creates isolation of the rest of your measures from the changes in the Transactions table (1) and the BridgeChartPLAccounts account mapping table (2). It also means that if you have made an error in the core P&L calculation, then you only need to fix it in one place. 

Supporting the P&L matrix we are discussing, there are two base measures: [BasePL] and [BasePLAllSales]. The former will return the P&L value for the given row in the matrix for all visible dates in the Dates table. The latter will do the same, but just for the sales accounts that you would include in the denominator of a percentage of sales calculation

In this example, [BasePL] is simply as shown below:

image of basepl_measure | accountingweb
basepl_measure

Note

  • In this case, the year-end journals are already excluded from the dataset, so do not need to be filtered out. If that were to change, then we would only need to change the base measure(s).
  • Line 6 filters Transactions to include only “Actual” and no budget transactions. In the example dataset there are budget transactions. 
  • BridgeChartPLAccounts is the table that maps the general ledger accounts to each row of the P&L matrix.

Dates Measures (8)

We discussed earlier that the Dates and Periods slicers (5) capture the user intent for the report but are disconnected in the data model. Four Dates Measures interpret the selections in the slicers in the P&L matrix to return the maximum and minimum bounds of the selected period, and for the same period of the previous year.

image of dates_and_periods_slicers | accountingweb
dates_and_periods_slicers

In that way, a single [PLActual] measure can calculate a P&L amount for any date range, and if we want to introduce new analysis periods, then we only need to change the slicers and the dates measures.

In the template, the core calculation for the [PLActual] measure is controlled by the code below.

image of pl_actual | accountingweb
pl_actual

You can see on row 47 that we are reusing the Base Measure [BasePL]. The Percentage Multiplier is essentially 1/[BasePLAllSales] if the calculation required is % of sales, otherwise it is 1.

MaxRelativeDay is a variable representation of a dates measure [MaxRelativeDay], and similar for MinRelativeDay. Dates[RelativeDay] is a numerical equivalent of Dates[Date], with 0 representing today, -1 representing yesterday, 1 representing tomorrow and so on. I use these relative day values instead of date values in my calculations because in certain circumstances they are easier to work with.

So, to support the matrix in this example, we are using four dates measures, as follows:

  • [MaxRelativeDay]
  • [MinRelativeDay]
  • [MaxRelativeDayPY]
  • [MinRelativeDayPY].

The PY values provide the same period last year.

Calculation Measures (9)

My Calculation Measures return the numerically correct values of the P&L calculations, based on all the slicer selections. The Calculation Measures supporting the matrix in the image, with the current slicer selections are:

  • [PLActual] returns the actual P&L amount for the selected period as a number or decimal fraction (depending on the column of the matrix)
  • [PLActualPY] returns the actual P&L amount for the same period in the previous year, as a number or decimal fraction (depending on the calculation slicer selection for SwitchCalculation
  • [PLVariance] in this case is [PLActual] – [PLActualPY].
image of actual_and_pct_of_sales
actual_and_pct_of_sales

In the template, the Calculation Measures each include two branches of logic: one for the absolute calculation option, and the other for the % of sales calculation. Hence the two columns above for “Actual” and “% of Sales” are both supported by the same Calculation Measure

The important thing about these Calculation Measures is that they return a numerical value (so that they can be reused by other measures), and that this value is numerically correct. What I mean by that, is that Gross Profit = Sales + COGS, where generally Sales is a positive value, and COGS is a negative value. That way, a positive variance (calculated as Actual minus Comparison) is always good news and a negative variance bad news.

Presentation Measures (10)

The final set of queries that are used to create the income statement matrix is the Presentation Measures. These control the display and formatting of the result. This will nearly always include returning values that are formatted as text, so you cannot generally use the Presentation Measures as inputs to further calculations.

In the example P&L matrix, I am using two Presentation Measures: [FORMATPLMatrixColumns] and [IgnoreMatrixColumnsPLVariance]. The latter simply returns the variance value, while ignoring the columns of the matrix. It is used to provide the banded conditional shading. The former, however, is doing more. 

  • It evaluates, based on slicer selections, which columns of the matrix are to be shown. 
  • It inserts the blank rows between the main P&L groupings.
  • It maps the correct calculation (actual, previous, variance and so on) to each column and applies the correct formatting.
  • In the case (as is the case here) where expenses are displayed as positive values in the report, then this additional multiplication by minus one for expenses is included where relevant in the Presentation Measures.

Note that this presentation measure [FORMATPLMatrixColumns] is reusing the calculation measures, such as [PLActual], which in turn reuses the dates measures and base measures that in turn aggregate and filter data from Transactions based on the P&L rows.

Summary

In this example, there are 21 queries in the dataset that are combining to produce the income statement matrix as shown at the top of this article. With this template, there are another 14 queries in Power Query that produce the dataset in the form that we want from the original data source.

Organising your queries in such a way that each has its purpose, with minimum duplication of code, will help to make your report flexible and easy to maintain. Small things like thinking carefully about what to name tables, columns and measures will also help you to maintain the report.

A downside of separating your measures in this way can be a performance hit, as well as if you get over-ambitious with conditional logic to give the end-user all kinds of point-and-click options. I would, however, recommend by starting with your measures split along these lines as it makes development and testing much easier. 

If needed, I would then look to optimise performance, by combining discrete measures into one (and also reviewing how variables are used within measures), testing as I go using the Performance Analyser.

How you structure your data model for the tables in the report, and how you write the measures will impact performance. It is possible to get over-ambitious with conditional logic to give the end-user all kinds of point-and click options. This can kill performance and a balance may need to be struck. If you have structured your queries well, though, making changes and trying new alternatives is straightforward.

Useful links

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.