Hello everyone. It's week two and we're using Dataset 007: Loans.
(Hello, I'm Janet and I'll be subtitling this
video too,)
In week one we went through the raw data.
Where's the .csv file coming from? How does it how its start to knit together?
I've built two tables and they look really
good. It was really simple.
(It looks like he knows what he's doing.)
This is the sort of thing we expect to be
building We've looked at their resources, their people and how many loans they've offered in a month.
What's the total value of those loans?
What are the loans for? Where is the breakdown of the loans? Where are our breakdowns
coming from? Where's the money going? All of those kind of things.
I'll cover how that's built today and I'll show you where we can expand.
Let's get into Power BI.
You can see where we were last week. Here's our table. The measures that I've written are pretty simple.
The main measure that you need to learn,
(it's in the description below)
is this filtered one. We'll investigate that next week.
One of the topics that you'll cover
(He's quite emphatic that he has absolute faith that you'll do this.)
(Squeak! That was a bit alarming.)
is putting in bidirectional relationships.
A relationship that goes both ways.
In a small table structure like this, where
we only have two, that isn't a problem.
But, when you get into the bigger star schemers
and beyond, for example Snowflake or even big enterprise-grade data modelling, bidirectional
relationships in the wrong place can cause chaos. Your measures will not work properly.
This is the way it will flow - from one to
many. If you think many should flow to
the one, technically it can, but the general
flow is the other way. This way only.
If you want to understand what that Case ID means in relation to to the offer.
(He's going to do that next week too when you get into process mining).
You have to find a way to override
this one-way system rather than say, I want it to work both ways, so I'm going to go into
the relationship and tell it to be bidirectional.
We deal with that by using this cross filter.
You'll say I want to know how many Case IDs I have in the Offers table using a cross filter,
based on the number of case IDs are in the mining. Say, we've found a problem area in
our process mining and I want to know how many processes that involves.
Cross filtering is going to allow me to filter the Offers table, which will then allow me to pull in
some more information. In this case it's not a huge amount of information we're able to get
In the real world, this is where you'd
be able to start to build proper tables with
information that will allow you to do that,
drilling back to understand what it means
and actually going forward. This one table of data we have from this loan company is never
going to be enough to do a full report pack, but the process mining capabilities are so
good I think it's well worth having a look at it.
So, the other two measures I've written. This is loans, which is account rows. And the Offers table,
which tells us how many loans we have, and remember account rows is a
really efficient little function.
Let's go to table and see how many rows there are. We've also done a sum, so I
want the sum of the request the loans amount. Again, I'm sure there are better ways we could
have done this and if we had proper access to the raw system behind it, we'd be able
to do something a lot richer than this. These two measures are what we want to do.
In fact, I'm just using these two measures to build these. There's one more thing I want to do.
Do you know what's missing here? A tree map.
We want to know how many loans by Loan Case ID. Break it down that way. This gives
a really nice visual representation of where the money we're lending is going.
Where's it actually going? That's vital. That's a really important way of being able to break down
and understand what's going on in your business.
Tree maps are quite new to a lot of people, but they are a really good way of representing the end value.
(Oh dear. He's going to faff on for a bit because he's chosen the wrong bit.)
(He's actually blaming you. Huh!)
So, £98 million in loans for home improvement
and £87 million in car loans. It's
a really good way to see what's happening.
From there I can interact with the other
visuals further down the page. This is key for you to understand.
I've done a couple of other things. Given the sheer number of people there are, I've compiled a top ten.
There are lots of resources in here. You may decide if we were doing this
in the real world, we'd probably say we'd
have something along here which will give
me a good way of looking at it. This is perfect. User 49 has 936 loans.
Let's call him Geoff.
Ross isn't doing as well (different Ross).
He's only got 527 loans. He's not very good at this job.
Maybe he should turn his hand to YouTube. 
(He may make a better fist of it.)
It all works out and the interactions and
capabilities between them are really good.
We can see, I've put a little flag on here
to see what the person is doing.
The flags are under conditional formatting. It's an icon. There's nothing really in there in terms of logic.
There are things I could do. I could look at making sure that it was year on year.
Month on month. I can have it on the
main table, based on whether the business is growing or not.
I did want to do more on KPIs with this dataset, but it's just too limited.
This is about process mining and I'll come back to that next week.
I've built this is around wanting to know what my loan agents
are doing, approving and what the loans are for. I could quite easily look across the table and check everything,
which helps. I can start to build up that
methodology which we can use the source in here.
Sort on the loan amount, so we can find out who's got the highest loan amount. It's Geoff with his 936 loans.
That's staying pretty close to the truth. Here we've got a switch.
Three and 18 are switched. There's not much in it but, this person has 72 fewer loans although they've done more.
What have they done differently? At some point you start to be able to
compare and contrast what's going on with our staff. How do you break them down?
Let's set up a comparison table. Pause the video now. And let's see how you're doing? Build a comparison
table yourself. Because, you've been following this closely. You can build a comparison table
that'll go through and allow me to pick and choose resources and see how they're doing
against each other.
I'll catch up with you in a minute.
(Or however long it takes you to do it.)
So, you've tried that. Let's have a look at
mine. In Power BI, you can get mixed up with some of these.
I've built this. It gives me
the option of picking someone.
Let's pick Geoff again. You can see how Geoff compares with the main group.
We can see a high percentage of his loans comes form existing loan take over.
A lot more are not specified. A high percentage of the 'unknowns'. There's
something going on there with Geoff which
might be worth having a look at. That's where being able to get to from numbers like this.
There are more tables we could build. We could do things here. We can start looking
at how we could build a profile which 
will allow us to compare and contrast.
For example, compare Geoff with Ross. 
(We need to know what's up with Ross before we fire him.)
It looks like Ross has made offers on a lot more of these existing loan amounts too. Home improvements are a high
percentage of his overall.  28% here. 27% there. 22% there. You can see, we need to find out what's going on.
There's a lot more that we can't tell from here. For example, does Ross have a young family?
Doesn't Geoff care about anyone? What's going on? That's what numbers will never give you.
You can start to see bad behaviours, though. Bad behaviours are what you need process-mining for.
I'm going to leave it here, for now, because we're at the point of a natural break.
This dataset has shown us what we can do. We can start to compare and contrast our people
and see who's doing what. Are we making the right headway? The question will always be
how do I know that my people are doing the best for me? Am I, as senior manager,
(Doesn't he have big hands? It must be a perspective thing.)
doing everything right? Am I leading
by example?  Are the processes, the way I've
set things out, being followed by
my staff? That's a key question for
a lot of people. Is the vision getting across? One of the best ways to see that is, is the process
working? That's a really difficult question
because people will say
'Well, you've got some KPIs here and you've got some KPIs there. They're being met.
What's the problem?' But the reality is, there's more to it than that. Process mining won't give
you all the answers. It will tell you
what's going on at this point? There's something
not quite right. This should take 10 seconds, but it's taken days.
We're going to look at process mining next week, so you'll see it in action.
It's a really interesting thing to watch process mining happen.
I hope you're going to stay with us.
Come back next week and it'll be good fun.
(Whoopee! He's promised fun. How can you resist?)
Have a great week. Catch you next Friday.
Thanks for reading.  See you next time.
