How do you relate to the numbers on your report? Can you visualise what it is trying to tell you? I’m going to suggest that for most of you, it will be a no.

How big does a number have to be so it becomes abstract? One thousand, one million, more? We’ll it is a lot lower that you think. Lets try a simple exercise to show you what I mean. Close your eyes and image a black background, and then a a white dot. (You can use whatever colours you prefer). That dot is now sitting in your imagination, now try adding one more dot at a time, and try to figure out at what point your ability to hold those dots in your imagination stops.

It should be around 15 dots, so that shows you what one version of abstraction is, there are other types. In the real world using your normal vision at what point can you look at a number of items, for example boxes before you stop to stop and count them. It is about 5 to 6.

So there are limits to our cognitive process to visualise and count numbers. As mentioned in my other blog posts, for example Your Brain & Report Design Part 1 – The Attentive vs the Intuitive Process, which goes through the how you have two path ways, the intuitive and the attentive process, looking at some items and being able to see the number and not count it, would come under the intuitive process (Don’t forget it can be error prone). Where as stopping and counting the items would be under the attentive process.

One good example of the movement of the attentive process to the intuitive process, is the understanding and recognition of dice patterns. Do you count the dots any more or just see the pattern? If you have very young children watch them as they grow up, to see the point they stop counting the dots on the dice and start seeing the pattern.

Reducing Abstraction

So that is one side of abstraction, our limit to visualise objects and quickly count things. The other side is the ability to relate to them, to turn them from an abstract concept to a number that has meaning to us. The number one offender is the percentage, but why?

In the book ‘Thinking, fast and slow’ by Daniel Kahneman, he shows the impact of something called frequency format and how it affects how we relate to a number. For example:

• 5%

It is nice simple low value, it is 5 out of 100. But as been mentioned above, we would find it hard to imagine 100 items, 5 should not be an issue. We just see the small number. Let’s change it to something more relatable:

• 1 in 20

Which do you find that you relate to more? The percentage or the ratio?

There is some body of evidence that when parts manufacturers, moved from expressing part failure rates as percentages, to a ratio, there was a lot more attention to the issues in the manufacturing process to drive those numbers down. But why?

Materialising the number as a ratio, moves it from an abstract concept of ‘how likely’ to the intuitive ‘how may’. You see this in adverts to make things personal to you. Does the poster say 16% of men may get prostate cancer? No it will say 1 in 6 men in their lifetime.

So moving from a ‘how likely’ to ‘how many’ number enables the person to understand, relate and visualise a lot more easily than an abstract percentage. However it can have its draw backs.

As part of the impact into frequency format, psychologists conducted a survey, were the question was asked which is the more dangerous disease:

1 – A disease that kills 1,286 people out of 10,000

2 – A disease that kills 24.91% of the population

The majority of people answered 1, the one that kills 1,286 people is the more deadly. If you stop to work it out, it is about half as deadly at around 12%. So what happened? People found it easier to relate to the number and make it personal.

Cat Food & Perfect Ratios

If you are of a certain age and from the UK, you may recall the adverts for Whisker Cat Food, which had the marketing line of ‘8 out of 10 owners said their cats preferred it’.

Lets break it down with three examples, that all have the same outcome:

• 8 out of 10 owners said their cats preferred it
• 4 out of 5 owners said their cats preferred it
• 80% owners said their cats preferred it

So we have expressed it in three ways, two ratios and a percentage. We can ignore the percentage, we have already discounted the percentage from our ability to relate to it. Which leaves us with two last options:

• 8 out of 10 owners said their cats preferred it
• 4 out of 5 owners said their cats preferred it

Which do you more relate to? Is there a good ratio to use? Yes, the power of 10’s. Use were you can ratios in multiples of 10s. Also round up numbers where you can. For example:

• 3.75 in 15

3.75 is 1/4 quarter of 15, but it should be expressed as:

• 3 in 10

Where we have rounded up 2.5 to 3. It makes a bit more sense doesn’t it!

Framing the number – Positive or Negative?

This is to make you think about the context of how you present the result. So, lets say you go to the doctor, and you have to have an operation, or an injection or something that will may have some sort of an impact on you. Does the doctor say:

• 9 out of 10 people have no complications after this surgery
• 1 in 10 people have complications after this surgery

Which would you prefer to hear? So framing is presenting the value in a way to make it a bit more or less personal. So for the example of manufacturing defects, would you use:

• 9 in 10 success rate
• 1 in 10 failure rate

Which should be focusing on? Well in this use case the 1 in 10 failure, that makes it more relatable to the situation.

As mentioned on abstraction and relating the numbers to you, if the Doctor is really good they’ll say ‘90% of people have no complications after surgery’

When to use a percentage?

As every good IT consultant will say, ‘It depends’. Something to consider:

5% Cash back on purchases sounds a lot better than 5p back in every £1 spent! You know it is a small amount , and easy to compare across other offers. The higher the percentage the better the deal. For cloud services 99.999% uptime, sounds a lot better than 0.9 seconds downtime per day. The more/less decimals the better/worse the service level. So when measuring your service level you are comparing the difference, not ‘how many’, you can see if it has reached the acceptable agreed service level or not.

Other uses of percentages

Sometime around 2014, there was survey of 10,000 secondary school children. They were asked what drugs they had tried. A few year later they did the same survey, again asking the same questions. The media was then full of news items like, ‘100% increase in cocaine usage in school children’, and ‘A resent survey has shown that drugs usage has doubled in a few years’ and so on. Looking at the data, it showed that it had increased 1 student, to 2. So technically it was correct. However not quite the sensation the newspapers would lead you to be.

In ‘Thinking, fast and slow’ it talks about that there is some evidence that in the US legal system, defense or prosecution lawyers will use one or the other.

• Defense – DNA evidence in capital cases is wrong in 1 in 1,000 cases
• Prosecution – DNA is right in over 0.01% of cases

You can now see what they are doing to two to relate, or disassociate the numbers to the jury.

Rounding up

You should have some insights on abstract numbers, and how by using ratios, you can move numbers to something a bit more intuitive and a lot more relatable. I always say when I present talks about subject like this, that for the intuitive and the attentive process, one is not better than the other. We just want to help people process the information quicker, and I will say it for ratios and percentages, one is not better than the other, just consider it for your use case.

References

Cat Image: https://unsplash.com/photos/mrTydVjg04o

Cashback: https://www.vectorstock.com/royalty-free-vector/cash-back-5-percent-money-refound-concept-badge-vector-28061818

Chair Picture: Jon Lunn (Me)

Thinking, Fast and Slow – Daniel Kahneman – Chapter ’Rare Events’

At Ricoh Digital Services (AKA the day job) we do a lot of Data & Analytics projects for a diverse range of customers. When the project is completed and handed over, we can let the customer run it, or we can support it. What we don’t get involved with is the changes that it can bring to an organisation. Sure, during the sales process, we talk about the single source of the truth, the outcomes, what the customer wants, but not how they use data internally. We assume that the Data Warehouse is going to be used, but what we don’t do is see, is if the organisation is using it effectively, we assume that it will be. However, what we see is a number of organisations reacting to information, rather that being lead by it. So here are some ideas I’ve gathered on turning that around, and moving from a reactive driven to a more proactive data driven and some of the benefits that it should bring.

The Problem

The above image is a picture of a hand loom, weaving thread into cloth by hand, a laborious time intensive manual process, prone to mistakes. Replace that hand loom with Excel, and that is a chuck of your organisation today. That is the problem, there is normally a small cottage industry of Excel users creating reports, running teams, departments, entire companies, and COVID-19 track and trace databases using Excel. In the wrong, and even the right hands, Excel can be dangerous. Did you know that Excel played its part in a $6 Billion trading loss at JP Morgan as one of the copied and pasted quantitative models had some basic Excel issues, as noted in the internal investigation:

Read More »

“Have you seen the deck.gl framework from Uber? It’s designed to show large scale big data visualization?” said one of the software engineering team. I had a look at it, and it does look amazing. The below image is from the deck.gl framework, and shows car accidents across the UK, or at least in the screen grab, the south east and London.

We went through some of the examples, and while looking at the flight data one, they mentioned that ‘You can’t do that in Power BI’.

I retorted, ‘You can do something a bit similar’ I then had to go prove I could do it. To be fair they had said that a few times while going through the examples, I had to pick one I knew I could do. There was no way you let the front end peeps lord it over us data geeks, what sort of a world would that be!

So first of all, I had to get some data. Thankfully the lovely people at Uber data visualisation, had provided the source of the data. One quick hyperlink later and I was downloading flight path data from the website The OpenSky Network. It’s a service that collects data from a network of stations that read the ADS-B and MODE-S signals transmitted from the aircraft for air traffic control.

It’s a big data set, it is split by day, and then by hour, and as it can be downloaded in CSV format, so I loaded the one file for the 07:00 to 07:59 data, that was about 200 mb.

Nice and easy so far, its a very easy formatted data set (check the readme.txt on the site), apart from the time columns. They are in the Unix format. For those not familiar with it, it is not a date time, it is the number of seconds that have passed since 01/01/1970. So 1574349827 is 11/21/2019 @ 3:23pm (UTC). That isn’t an issue with a bit of M we can convert it on load in Power BI to a nicely formatted date time format.

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [time])

Once that was done, I mapped the points on the map visual which gave me the following output:

One problem, there was to many points, so I’ve filtered the data on the Latitude and Longitude columns just to show a range for the UK. Looks good, but we need to animate it. Does Power BI do animation? No… well not by default, but there is a custom visual called Play Axis that can enable you to do it.

This allows you to add a column to it and play it in order, in this case my date time. In fact I didn’t need to convert the Unix time, as it would have played it back in numerical order! Once that was added to the report we have animated visual awesomeness!

I’ve loaded the sample file on my GitHub here enjoy!

In my Power BI courses I always recommend some books and sites that will help them learn DAX or M. Most of the time i’m presenting to new users to Power BI, and having to get them to move from the Excel muscle memory that they have and also to show them that sometimes the code-less approach is not always the best way.

One example presented itself the other day. A user was combining some Excel sheets and wanted to create a rough dimension based on the values in one of the columns, then use it as a filter or axis. They hit the internet and came across a few post on how to do this. A few recommend this approach:

1 – Append the two tables together as new query
2 – Remove the columns you don’t need
3 – Remove duplicates

Well that is sort of fine, as you can’t specify a single column in the table append, and it can all be done in the interface. However is has a bit of overhead in the 1 and 2 stages, so can you do it so you only reference the relevant columns and remove some of the overhead. The answer is ‘Yes’, but you need to code it in M. Using the Blank Query function i created this M code:

let
   Source = Table.Combine(
                            {
                                Table.SelectColumns(#"table name 1",{"column name"})
                            ,   Table.SelectColumns(#"table name 2",{"column name"})
                            ,   Table.SelectColumns(#"table name 3",{"column name"})
                            }
                        ),
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"

So lets look at the M code behind it.
So rather that use a query as a reference, you can declare the table and column that you want with the following

 Table.SelectColumns(#"table name 1",{"column name"})

You wrap these up with a table combine to a SQL Union the columns together using the following

Table.Combine

than run a remove duplicates to do a SQL Union All. You don’t have to duplicate the whole tables/queries just to select the data, then trim it down to the columns that you want. As far as I can see you can’t do this in the interface, it only via M.

This blog post comes from a question raised at a Power BI training session, and deals with updating data source information, for example moving from development to production, or folder or file locations moving. The question was asked as I was showing the attendees the ‘Load from Folder’ function in Power BI, so you can dump monthly files into a folders and load them together. The question was  ‘What if the location changes? do you have to rebuild the report?’. I mentioned that you can edit the M code to change the location, but the users was a bit worried that it was a bit to ‘techy’ to do that, so  I suggested a parameter that you can use. They where more than happy with that approach it  turns out.

So how to use parameters? Well lets fire up Power BI and enter Power Query, but clicking on ‘Edit Queries’

And then select the New Parameter option, which should bring up the following…

Now, there are a few options here, you can have a list of values, or items based on a query, but for this example, we just need the basic version.

Type = Text
Current Values = The folder location that we are going to use to pull the files in

Click OK and how do we use it when loading a folder?

Select the folder import then select the little drop down for the folder path.

Select the parameter (Or start the process from there, by creating a new one).

Click OK, and away it goes as if you typed in the folder location normally.

So if you need to change the location, you don’t have to edit the M code, just update the parameter, by clicking on the manage parameter selection, where we created the new parameter. Nice and easy, well I think so.

So parameters can be used in a number of data sources, for example SQL Server connections

So switching around DEV, UAT and Live servers should be a slightly less painful process, than editing the M code.