Welcome to the final video in this tutorial and the module. Okay. so in this
video we're going to build our very first Power BI dashboard, so in order to
do that what we need to do first is actually save the data export that we
created here. So I'm just going to go: file, save, enable saving and I'm actually
going to save it as a file on my desktop so it'll be easy to find.
So: desktop, save. Okay, perfect. What we need to do then is we need to
open up Power BI, so please go ahead and do that and we should see this screen
pop up so we can either get data here or get data here. That's going to be our
first step. We're going to import our excel file, so I'm just going to click on
'get data' and then this window is going to pop up. So I'm going to select Excel,
connect. I'm going to go to my desktop and I'm going to double-click my Super
Simple Survey Excel export. Great! Then it shows me what's inside the Excel
workbook, so I'm going to select forms and load. So it's going to load my forms
and there we go. So here inside the fields pane I can see hey I got one
query called forms with the following column names, it just automatically identified that.
Here in visualizations we can start creating a number of different
visualizations, so let's go ahead and do that.
Let's start with the simplest visualization possible and that is this
one, it's called 'Card', so let's select it and it will show up here. And so the
simplest visualization that we can do is: I want to know that
number of people that I've interviewed, and in this case obviously it's test
data so I didn't actually interview anyone I just entered a bunch of random
names, but still I want to know how many names are in my system. So we could
either do that by selecting the name column or, even better, we can do it using
the form ID column. So I can just drag this form ID into fields and then it's
going to automatically show me the very first form ID, but that's not much use to
me so I'm going to just click on first form ID on the little arrow here and I'm
going to select count or alternatively count distinct which would be even
better which means it's going to count the number of unique form IDs and since
all of my form IDs are unique it shouldn't matter whether I select count
or count distinct. And there we go! So I know that I have eight forms in my data
set. In other words I have eight people that I interviewed, so instead of having
this count of form ID label here I'm going to change this. I want to know the
total number of respondents, that's what I wanted to say. So in order to do that
I'm going to click on this little button here which is my format pane and I'm
going to turn off this category label selection and then I'm going to turn on
this title selection and I'm going to expand that by clicking on this little
arrow and then we can choose the title text, so my title text is going to be
"total number of respondents". Perfect! But it's a bit small for me and it's not
centered so I'm going to center it and then I'm going to increase the size to
15 to make it a proper title and then increase this. I've also moved this box
to the top left because we always want to try and make the most use of the
space that we have on our dashboard. Great! So there you go that's our first
visualization and as you enter more data, it could be test data or real data, and you
export your file again, this number will also automatically change. So what could
be the next step the next visualization, well how about the number of pets. So if
I drag over and make wider my fields pane I could see the different columns
that I have. So remember I have date of birth, whether or not they own any pets, their
name, where they would most like to visit. So let's use this: "Do you own any pets
one?", and that's a yes/no question so you know a perfect visualization for yes/no
questions is the pie chart, so I'm just going to click in the blank space here
to make sure I'm not selecting any of my visualizations and then I'm going to
insert the pie chart but just simply clicking on it. Again I'm going to move
it around a bit that's fine and then here we see the different fields that
are available to us. So we have the legend, we have the details with the
values and then we have the tool tips. So what I'm going to do is I'm going to put
the form ID into the values because I want to count how many instances I have
and then in the legend I'm going to put "How many pets do you own?". No, sorry I'm
going to put "Do you own any pets?" and there we go it looks like about 3/4 do
own pets and if we hover over the yes portion of this pie chart, we can see
that six of our submitted forms have responded "Yes" and that's 75% and two
have responded "No" which equals 25%. Great! So this title isn't very nice so again
I'm going to go to the format. I can then click on the format button and then in
title I'm going to expand this and I'm going to just simply write out the
question, so: "Do you own any pets?" Great! And again
I'm going to center it and make this 15. There! That's looking nicer already. Okay,
so what other columns do we have here, what other questions do we ask? Well
how about the name and date of birth, maybe I just want to display this like I
would in a regular Excel sheet, so the way we do that is by selecting a table.
So I'm going to insert the table, I'm going to make it a little bit bigger so
it reaches the bottom and I'm going to drag in the name and the date of birth.
Okay, great! So I have my name and then I have this yea,r quarter, month. Well that's
a bit strange because I'm not really interested in what quarter they're born
in I just wanted to display the date so we can really easily change that here. So
as you can see we have form dot date of birth with the year, quarter, month and
day and if I again click on this little arrow I can say hey I don't want this
date hierarchy I just wanted to display date of birth. Perfect! Okay, so we have
our little table now. Another visualization that we can enter is maybe
a bar chart showing how many pets each person owns, so let's select this very
first visualization. Again, I'm going to click in the blank space first and then
on the first visualization I'm going to make this nice and big, nice and long, so
we can properly see what's going on and so what I want to see is I want to see
the name stand by the axis and then the values, i.e. the number of pets, along the
x axis. So let's put name on the axis and then I'm going to put "How many pets do
you own?" in the value and now something interesting is going on. We see zero to
one and so it seems like everybody only owns
one pet, but we know that's not true we know they actually own, you know some
own one sure, but others might own three or even twelve. Okay, so let's see what's
going on here, we need to go to our query editor. So I'm going to go to my query
editor by clicking on this button here 'edit queries' and there we go we're
inside a query editor we can see all the different columns that are selected and
here we can see all the different query steps. So if we click on these individual
steps we can see okay we identify the source first, then we went into
navigation. We, in the navigation steps the column headers are not yet
identified as such so it automatically promoted the headers and
then it went and changed all the types so if we go back to the promoted header
step we can see hey, you know, none of these data types have yet been defined
but when we go here at the change type we can see, okay, it's recognized form ID
as a text column, it's recognized date of birth as a date column and then here "How
many pets do you have?" it's recognized as a text column and why is that? That is
because we have these null values but they're not really no values in Commcare
they're exported as three dashes so we need to change those dashes into null
values and change the data type into numeric, so let's do that. I've selected the how
many pets do you own column and I'm going to go to transform, replace values
then in the value to find I'm going to enter three dashes and replace with 'null'.
So null in Power BI is a special value. It's not zero but it means, it tells our
dashboard that there is just no value in that cell.
Let's press OK. All right, nice. And we can see that null is a special value
because it's italicized and then here this ABC we're
going to change it to a whole number. Great! So let's press home, close and
apply. Okay, so nothing much has changed yet although two of the names disappeared.
Reply no. And that is because in the value it's still registered as
count of how many pets you own and we just want that to be the sum and there
we go. Now we have a table or a graph that makes sense.
Okay, so again I'm going to change the title here by clicking on the pen
formatter. I'm also going to turn on data labels to show me quickly like okay
that's how many pets everybody owns. Then I'm going to change the title, so it's
going to say "How many pets do you own?"
center it, 15 and there we go. Okay, so what are some other visualizations that we
can put in here. How about a map visualization? So we can insert the map
visualization by clicking on this button here map again I clicked in the blank
space and then I'm going to select map I'm just going to put that down here.
Make it a bit bigger and now we can enter some, sorry just click on it, so now we
can enter all of these values, all of these fields are linked to this map
visualization. So what is the first one? It's location so for us that is where
would you most like to visit, so let's drag and drop that in there. ah! So now we
see for each country a little blip showing up. That's great.
But we also want to know for each country, we want to change the size of
the bubble to reflect the number of people that want to go to that country.
So for value precise, I'm going to insert form ID again and there we go! So two
people want to go to Japan, two want to go to Brazil, two to the US and one to
Kenya and same for India. Great and again like for you it might be different and
that is because you might have responded differently when testing the application.
Then I want to add a bit of color to this map so in the legend I'm going to
put the same one again, "Where would you most like to visit?" and then we have the
different colours. Great! So let's make it a bit prettier by clicking on the format
button and I'm just going to open up the legend tab here and I'm going to
remove the title. Okay, so now it's just Brazil, Japan, USA, India, Kenya.
I'm going to make it a little bit bigger so maybe 10 and then again the title with
the question "Where would you most like to visit?" or "Where would you most like to
go on holiday?"
Great! And again I'm going to center it make it 15.
And another thing we could do is turn on the category labels so now it shows us
which country each of these is. So a final graph that we can put in here is
maybe a scatterplot, so that's when we want to compare two numeric variables.
So, one thing we can do is we can plot the age against the number of pets to see if
there's a relationship. As people get older do they start accumulating more
pets or is this just a myth, so there's one problem we only have one you know
numeric variable in our inner set we also have this number but that is just
the row number so that doesn't mean anything and so we need to create
another number, we need to basically create an age number. So how do we do
that? Well we go back to our query editor, so I'm going to click on edit queries
and what we're going to do is we're going to add a column. So here we have
the date of birth column and when I've clicked on the add column tab, I can
create a new column that extracts only the year so let's just extract the year
from this date of birth column. So again I went date and then I selected year, and
there we go! So this is their birth year and if I want to now calculate the
age what I simply do is I create a custom column I'm going to call it age
and then we'll say it's the year, so that's the birth year, no sorry so we say
2017 - the birth year, right? And obviously there's a way to do this more
dynamically, I mean we can enter a formula here which automatically tells
us what the year is now but you know for the purposes of this demonstration, let's
keep it simple. I just say okay 2017 - the year is going to give me the age and
there we go: twenty nine, thirty eight, twenty eight,
Great! One last thing we need to do is change the data type here, so we're going
to change it to a whole number and now we'll be able to use it in our query.
Let's go to home, close and apply. Great! So again I'm going to click on the blank
space and I'm going to enter this one, this visualization with all the bubbles
which is our scatter chart. Great, I'm going to enlarge it a bit and again it
shows us the values that we can enter. So what are we going to enter first? I mean,
so we have the x-axis and y-axis right? So one of those is going to be "How many
pets do they own?" and the other is going to be age, so let's put age in the x-axis,
and then let's put how many pets that you own in the y-axis.
Alright, great! However, we get only one point and then 30, 20 to 30, on the y-axis
and something like 300 on the x-axis. so what's going on here? Well first of all,
we don't want to know the the sum values we want to know the average
values. Okay, so this makes a bit more sense, right? So the average number of
pets is 4.3, the average age is about 36 but still this isn't quite what we want.
So up here we have the details at tab and this is where we're going to put our
form ID because that's the level of detail we want, we want every single
person to be represented here. So here 12 pets that's clearly Omar Sy, and here the
oldest person with two pets, that might be Mary or Nastio. Okay, and one more
thing that we want to do here now is we want to add a trendline to see well what
is the relationship and we can do that here in this third tab: analytics. So
let's click it and then first option is a trendline we're going
to add it and there we go. So a very weak trend between the number of pets owned
and age. Okay, great! So another thing I see here is that you know this y-axis
has a bit of an ugly name and so we can change that by simply coming here and
this is how many pets you own, right click and I click on rename. So I'm going
to make this nice: "How many pets do you own?" and there we go it's changed it.
And again let's change the title of this scatter plot by clicking on title,
selecting the title, deleting it and then we'll say "pets owned versus age".
Again, I'm going to center it and make it 15. Great! And so we've set up our first
dashboard. Here I'm going to quickly change the name of this tab by double
clicking it and I'll say Super Simple Dashboard.
Excellent! So that's it and feel free to click around on your new dashboard, so if
I click on, you know, the the different pie charts is going to update all the
other graphs so if I'm only interested in people that do have pets I can see
that it was six people. Here are their names and then our, you know, where would
you most like to visit map will also update accordingly. If I click on one of
the individual names, for example, I could see like which country did they want to
go to, did they answer yes or no and some more details. So that's pretty much it
for this video, I hope you enjoyed this module in general and we'll see you next
time for our Commcare beginners module. Alright, bye!
Không có nhận xét nào:
Đăng nhận xét