Do not be scared of Power BIby
As irrational beings, our deepest fears come from the unknown and when you first start working with Power BI, you will get some unexpected answers. However, there's actually nothing to fear, writes Hugh Johnson.
Unlike ourselves, Power BI is completely rational. An unexpected answer is simply a symptom that you need a better understanding of your data, Power BI, or your report.
Here are six things that used to haunt me when I first started working with Power BI. I still have the occasional new nightmare, but if I treat each scare as a learning opportunity… what do they say? “What doesn’t kill you makes you stronger”.
All my customers have the same sales total
Probably the first unnerving surprise Power BI ever gave me was when it told me that all my customers’ sales numbers were the same. I can illustrate this very simply. I have a report that contains two tables: ‘TransactionLines’ and ‘Customers’.
I want to create a visualisation to show sales by customer, so I created a simple measure to calculate [Sales] as follows:
Then, I drop Customers[Customer] and [Sales] into a table to see sales by customer.
What I get is a report that implies that each customer has sales of £7,395,362.10. I also see that the total sales number for all customers is £7,395,362.10.
Scary? Well, almost certainly “wrong”. Well, let’s get back to rationality. The calculation is not wrong. It is just that I am using it in the wrong way.
If you ever see anything like this in Power BI (and you will), you can be almost sure that it is a relationship (or lack of relationship) issue. I have put a list of customers into a table, alongside a calculation for sales.
The thing is these two columns have nothing to do with each other. My [Sales] measure knows nothing about customers.
Fixing this is very simple. In this case, you don’t need to modify the calculation at all. You simply need to tell Power BI how ‘Customers’ and ‘TransactionLines’ are related.
Having created a 1:Many relationship between ‘Customers’ and ‘TransactionLines’, based on a common CustomerID, my report now shows what I expect.
The total is not the sum of the parts
We saw this already in the above problem, where each line has a value of £7,395.362.10, and so does the total.
This example is quite obvious, but it can be more subtle than that. How can it be that the whole is not the same as the sum of the parts? The answer is very simple, and also very powerful. Power BI calculates every number that it displays independently, based on something called the Filter Context.
If the “Total” at the bottom of a table happens to be the same as the sum of all of the rows (and it often will be), then so be it. This is not how Power BI calculates “Totals”, and it is a good thing too.
Imagine for a second if you added another measure to calculate the gross margin percentage for each sales transaction. For simplicity, let’s say that every transaction has 25% gross margin.
I would want the table to look something like this:
Now my model has 1,183 customers, so there are 1,183 rows in the above table. If the “Total” at the bottom of the table were simply the sum of all of the rows, then I don’t want it to show 1,183 x 25% = 25,975%. Nor do I want it to sum up 25% x 35,128 (the total number of sales transactions) to give 878,200%.
Clearly, I want to calculate the margin percentage based on all of the sales transactions together.
So, it is a good thing that the numbers don’t just “add up”.
When you get a little bit more advanced with Power BI, you can also manipulate the Filter Context to do things like display sales and costs as positive numbers, yet still get the correct totals.
My slicers contain “phantom” blank values
You may often see a situation in your report where a slicer includes unexplained blank values, even though the column you are slicing on does not contain any blank values.
When I first saw this, I was really puzzled. If I select “(Blank)” as my Financial Year, I still get a result in my report, even though I don’t have any blanks in my FinancialYear column in my Dates table.
As with many things Power BI, there is a simple and rational explanation. The explanation is that I have transactions in my model that fall outside of the range of my ‘Dates’ table. I can fix this in one of three ways:
I can expand the range of my ‘Dates’ table
I can use QueryEditor to exclude transactions that fall outside the range of my ‘Dates’ table. This is my best option if I am not interested in these transactions.
I can add a quick workaround with a visual-level filter on my slicer to exclude all “(Blank)” values for my Financial Year.
If a visual, or visuals, that you expect to show data just return blanks, it can be a bit unnerving.
I know that in my model I have data for the financial year 2019. The explanation is that you most likely have a filter (or filters) problem. In other words, the measures that you are using to render your visuals cannot see any data. In this case, check the following:
- Visual-level filters
- Page-level filters
- Report-level filters
- Any filter conditions (most commonly invoked using the CALCULATE function) used within your measure(s) that will modify the evaluation context.
- Relationships in your model that will propagate filters through your visuals as they are rendered.
Trust me, there will be a logical and rational explanation.
Halloween is in month 11
In the example below, I am showing departmental sales by month.
You can see the problem – they are sorted alphabetically and not by time. The problem is that I am using a Dates[Month] field that gives me the abbreviated form of the month name (to save space on the report). The formula that I used (in a calculated column) is as follows:
Now you would hope that since Power BI understands that Dates[Date] is a date, then it would also understand that our new column Dates[Month], which is derived from Dates[Date], would contain months. It doesn’t.
The FORMAT function will always return a text value and Power BI will then by default sort it alphabetically. Getting round this is easy.
You can simply define another column with the sort order for Dates[Month] and sort by that. You can also use this technique to adjust the sort order to reflect your financial year. Here is a video that explains how.
Spooky transaction numbers
I have table visualisation below, showing sales transactions by month.
When I cross-check the numbers, they are correct. The problem is that when I search the database for the first transaction number 258145012 it is not there! The answer is a subtle, yet simple one.
The clue is that at the bottom of my table visualisation; Power BI has added a total for the TransactionLines[TranNumber] field.
Unless you tell Power BI to do otherwise, it will always assume that you want to aggregate any numbers with a SUM calculation. In my data model, TransactionLines[TranNumber] is stored as a number and so Power BI adds them up. It is showing the aggregate sales value and transaction number for every combination of customer and month.
If I turn off the auto-summarisation on TransactionLines[TranNumber], then I can see that actually I have several transactions for A F Browne Chemists for November, each with the correct TransactionLines[TranNumber] displayed:
Here is a good video from Microsoft that explains how the auto-summarisation feature works and how you can control it.
For a rookie, starting out with Power BI, there are a number of things that might spook you. However, there is always a perfectly logical explanation for these.
If you keep the faith and always try to understand why Power BI behaves in the ways that it does, then you will learn to love and take advantage of these features that are usually there for a very good reason.
- Power BI: A beginner’s guide to measures vs calculated columns
- How to create and manage relationships in Power BI
- Power BI basics: an introduction to the Filter Context
- Your Power BI P&L report: How to handle expenses as positive numbers
- Power BI basics: How to create a simple, dynamic and flexible dates table
- Power BI Basics: QueryEditor or DAX?
- YouTube video: Sorting your months according to your financial year
- YouTube video: Default summarization in Power
You might also be interested in
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...