- 4 months ago
Take your Power BI skills to the next level with this Advanced Data Modeling tutorial! In this comprehensive walkthrough, you'll learn how to build powerful data models and create interactive, professional dashboards using advanced visuals and UI elements such as *Stacked Column Charts, Pie Charts, Cards, and Navigation Buttons*.
Perfect for data analysts, BI developers, and business professionals*, this video teaches you how to *structure your data, design with intent, and build dashboards that deliver insights at a glance.
Perfect for data analysts, BI developers, and business professionals*, this video teaches you how to *structure your data, design with intent, and build dashboards that deliver insights at a glance.
Category
📚
LearningTranscript
00:00Yeah so this is the data model these are the questions which we are going to solve today
00:12the first step is we are going to create this data model after that we are going to solve
00:17these questions and then we are going to visualize okay so let's do that.
00:21So open a new power bi desktop file and then you can click on get data connect to these
00:30data sets one by one get data again and mcell put a checkmark on both the tables load get
00:50data again trg okay so five tables I have now and now let me first of all go to the
01:03data model section and you can see that some relationship has been created by power bi
01:10because power bi has a daughter detection feature as well so what I would like to do
01:17so I would like to create this kind of a data model so I'll put m table in the center okay
01:25and just salary on the left department below salary okay pricing and training on the right
01:37hand side surprising okay so let's try to now validate the relationship which power bi has
01:50created first of all I will check whether the relationship which has been created between
01:55sal table and the department is on the basis of department code okay which is absolutely correct
02:01no problem in that okay and this is as per my diagram as well then between salary and the
02:08amp table the relationship has been created on the basis of amp ID which is also absolutely
02:13correct no problem then between amp table and the training table the relationship has been on the
02:19basis of amp ID which is also correct but what I see that the training and the pricing tables
02:25have not been connected and let me show you the data for that so in the training I have this column
02:33module and in pricing I have the topic so the names of the column are different and that is
02:39why power bi was unable to create a relationship because the names of the columns are different
02:45so here I have module and in the pricing table I have topic so I need to manually create a
02:51relationship so what I will do I will drag module and drop it onto the topic and a relationship has
02:59been created and if you would like to validate right click and properties okay so this has been
03:06done and now this is my data model so what kind of relationship it is cardinality is one too many
03:14here so now the next thing is your data model is ready so one thing I would like to see the
03:21department code is text department is text and the head these are the head of the departments
03:29for example department number D1 is headed by this 1003 employee so for the head although
03:38it has numbers but I would like to treat head as as a dimension I don't want to do any calculation
03:49on the head okay so I would like to treat it as a qualitative data even though it has numbers
03:56so what I would like to do I would like I will change the data type from whole number to text
04:03why I am doing that so that when I am using the head column power bi does not do any kind of
04:12calculation on this column or otherwise what power bi will treat it as a numerical column
04:19and will do calculations like aggregations like some or anything which it feels suitable I don't
04:27want to do any aggregation on this column so I am changing in the data type to text let's go to the
04:33amp table amp is whole number name address phone number email now data birth data type is date
04:47absolutely correct and data joining the data type is date absolutely correct next rising table
04:56topic text certification cost whole number which is absolutely called absolutely correct but I
05:04would like to add a currency symbol to this column so I'll go to format and I'll change
05:12it to currency so as soon as I selected currency from the format a dollar symbol was added to this
05:20column let me go to the salary table the amp ID is whole number the department code is text
05:30designation is text salary is whole number the data type is correct but once again I would like
05:38to add a currency symbol to this column so I'll go to format click on the drop-down and I will
05:46select currency current designation text training amp ID whole number module text status completed
05:56or status is text marks is whole number so what I have done I have validated the data type of all
06:04the tables which I have in this model so now let's try to see the questions the first is training
06:10analysis first question number of trainings applied under each head for example I would
06:18like to see how many trainings have been applied under each head so if you see this is the table
06:25I have where I have the status of the trainings done by the employees okay so this is the training
06:31status in this table and if you talk about where do I have the head because I need to see number
06:39of trainings so this is I can get from the training table each head head is available in
06:47my department table this is the column for head okay so since we are so we have created a relationship
06:55so I can easily visualize the data even if the columns are in two different tables so what I
07:04will do now let me create a stack column chart now you can choose the visual as per your choice
07:10and my question is number of trainings applied under each head so what I will do I'll drag head
07:17from the department table into x-axis and from the training table I would like to see the number of
07:27training so I'll drag e ID into y-axis and this is count of employee ID okay so now I am getting
07:36I have got the answer which is number of trainings under each head let me go to format and let me
07:43switch on the data labels okay so this is my first visual let me change the title which is
07:51instead of using this word count of ID so I will say number of trainings by head let me
08:00center a line increase the font size okay text color is done and font I'm using UI bold let me
08:12add some effects okay so I saw we have solved the first question which is around finding number of
08:20trainings under each head let's see the next question number of trainings completed under
08:26each head now if you see my training table so we have two types of status we have a status column
08:33and we have the training is either completed or it is pending okay so either number of trainings
08:40completed only so one way is I'll just make a copy of this visual control C and control V and now I
08:49only want to see the status of status or completed so what I will do I'll drag my status column and
08:56drop it onto the filter on this visual section and I'll put a check mark on completed now my
09:04second visual it is only showing me data for the completed training for every head now let me change
09:12the title number of trainings completed by head okay so that is how you are able to visualize so
09:20this is one way okay so other way is that from the training table I can also do the same thing by
09:28writing a DAX formula let me show you how can you write do the same thing using a DAX formula as
09:34well so I can just click on this option new measure I'm creating a new measure and if I
09:43want to count so I'll just say training completed training completed count so in order to do that
09:51I'd only want to calculate amp IDs but where the status is completed so I'll use calculate function
10:00count amp ID from the training table okay so this is the training table comma but only where the
10:11status is equals to completed so I'll take status training status is equals to complete close the
10:19bracket okay so this is the way I got a new measure call as training completed count and you
10:28can use the same thing okay for example let me create stacked column chart head I will take from
10:38the department and I will use my measure training completed count into YX and let me switch on the
10:47data level so I got the same output so I think 27 26 22 yeah 27 yeah that's correct so let's
10:58do one more thing just not a requirement from the questions perspective let me try to show
11:05that training completion percentage through the DAX so I'll just once again go to my training
11:14table click on new measure and I will type a name training completion percentage and let me do that
11:25let me use my training the measure which I created which is training completed count divided by total
11:34training so I'll just count all the employee IDs in my training table okay and enter so I got major
11:45called as training completion percentage and if you see let me create a card and this is training
11:53completion okay this is count training company so it is showing me 0.56 percent so I need to show
12:03it in percentages I will go to the format and I will select the format as percentage when you
12:12will select the format as percentage it will multiply your formula by hundred and will also
12:18automatically add a percentage symbol okay so let me do that so I'll just select percentage
12:24okay and now you can see it is showing me in percentages and also has it has added a percentage
12:32symbol to my output okay so that is one way you can use that okay so we have created that training
12:40completion percentage as well okay so maybe we can show one card on the top as a summary
12:48okay so let me put it here and training completion cannot count so which is 56.55
13:00point six seven percent and let me switch off the category label title which is
13:10okay and let me give some color also okay and this is my so let me use a green color
13:25because this is completion okay so I just created one card as an add-on let's see the
13:34next question training result of each head so I want a result and if I go here to my training
13:44table I don't have the result I only have the marks so what I need I need to complete I need
13:51to prepare the result but there are some problems for example if you see wherever the status is
13:57pending the marks are showing which is not correct because wherever the status is pending marks
14:04should not be there right so I need to fix this problem first of all in order to prepare the
14:13result so let's try to fix this using if else statement so I'll go to my training table and
14:22now I'll click on this option called as new column given name to this column as final marks and I
14:32use if status training status is equals to pending output should be zero otherwise whatever is the
14:43value available in the marks column take that as it is so I'll select my marks close them so I
14:53got the answer so now you can see wherever the status is pending I got the answer as zero and
15:03completed I have the number as it is from the marks column okay so that is how I was able to
15:13adjust this problem in my final marks column now let's prepare the result if a student has scored
15:21more than 85% marks let's say he is passed if his marks are less than 85 then the result is reappear
15:31or retraining and if the marks is zero and a not applicable okay so let's do that once again I
15:43want answers for every row so I will click on new column and I will say result if final marks is
15:52greater than equals to 85 the answer should be passed if marks or final marks is equals to zero
16:03and a otherwise reappear okay or you'll be training and I'll close the bracket twice okay
16:12so I got the result so final mark 85 pass less than 85 reappear and wherever zero is there I am
16:20saying it is any so I will just use the nested if now I have the result now let's try to see the
16:29question once again training result of each head so let's try to visualize this let me make a copy
16:39okay and this is my now training result so I already have I don't want the status as here
16:52so I want to see training result of each head so what I will do I will drag my result column
17:02into the legend and this is a bifurcation of the each and every head so for example 1 0 0 1 has
17:1324 and a 12 pass 14 reappear so this is I preused a stacked column chart but if you want to show
17:23these columns side-by-side so you can use clustered column chart as well so let's use
17:28clustered column chart and this is how you can show that separately okay so this is clustered
17:37column chart so let's use clustered and let me give a title so this is training result by head
17:45okay so this is also done let's see the next question number of company-sponsored trainings
17:53under each head and the criteria to check whether this is company-sponsored is not marks should be
18:01greater than equals to 95 okay so if any student has scored more than 95 marks the training will
18:07be funded by the company okay so this is so what we need to do we need to find the company-sponsored
18:13status so what I will do now once again I'll go to my training table and I will add a new column
18:21and I will give a name to this column as company-sponsored question mark if final marks is
18:29greater than equals to 95 output should be yes otherwise output should be so I got the answer
18:37company-sponsored and I have the answer as yes or no now I can use this as a filter I can use this
18:43column as a filter so number of company-sponsored trainings under each head so we only want to see
18:48the number of company-sponsored trainings I'll go here and I will use that column which I created
18:57as a filter company-sponsored and I will drag it onto the filter on this visual section filter
19:04on this visual so company-sponsored filter on this visual and I will put a check mark on yes
19:10and this is now visual showing me only the data for company-sponsored trainings so let me change
19:17the title so number of company-sponsored trainings by head okay so this is how I have completed this
19:26one so let me give a name to this page as training analysis okay and let me give a title
19:33okay and let me put it and let me increase the font okay and okay so this is my training analysis
19:45page so maybe we can we can create some more cards later we have completed this part the number of
19:52company-sponsored and now let me go to the second page in the second page the next question is
19:59training applied for each module okay for the module so if I go here so module is this one
20:07and the training supplied for each module so for that let me make a pie chart in fact let me make
20:15a duplicate of the first page so that I don't need to do formatting once again duplicate this page
20:22and let me delete yeah let me delete this one and in the in this one this let me rename this as
20:32cost analysis and now here I will delete all these things and let me show in part in a pie chart
20:41for each module so what I will do in the legend I will put the module
20:48and count of employee id into values so this is trainings applied number of trainings by
20:55module so I'll change the title okay and let me just do some formatting so I'll go here
21:06I'll I don't want to show the legend and I'll go to my details label and let me show category
21:13and let me show category and percent of slightly increasing the font
21:20and making it bold okay so number of trainings by module
21:26percentage of self and company-sponsored training so I have already created one column
21:35where I have the status whether that was company-sponsored or not
21:39and since I want to show it in a percentage so either I will use a pie chart or a donut chart
21:48so let me do that so I'll delete this one and I'll take a donut chart self and company-sponsored
21:56training so I'll put my company-sponsored into the legend and count of employee id into values
22:04yeah and let me now here the legend is required so I'll just go to format and I'll switch on the
22:10legend okay and let me change the title number of company-sponsored trainings by module okay so
22:19let me make a copy of this okay so we have done the training analysis the next question is
22:27department wise total salary now in order to see the salary I have another table let me show you
22:36so this is the table called as salary where I have the salary information of employees
22:41and what do they want to show department wise total salary and department let's see where do
22:47I have the department so here I have the department code and the department name
22:52and salary is in the salary table and I have the department code over here as well so it means that
23:00they are linked so let me create so department wise total salary so let me create a
23:08column chart so I will take a department from the department table and
23:16sole salary from the salary table and this should be stacked column chart
23:24okay so this is how let me add some different image here so maybe we can use some infographics
23:36and let me delete and let me insert a shape
23:41maybe we can use this one coins and you can also yeah you can just show it like this
23:51and the title is the salary by department okay so we have done this question as well which is
24:01total salary by department department wise cost of company-sponsored training now this one I will
24:10like to see the department wise cost of company-sponsored training so if I go to my training
24:17okay so I don't have for every module there is a certification cost there is a cost okay so cost
24:25and the cost is available in this table called as pricing where you can see power bi has a cost
24:32let's say five thousand dollar tableau fourteen thousand so these are the cost and my training I
24:38have the company-sponsored so what I will like to do I would like to bring in the cost
24:46from the pricing table in my training table side by side so that I can filter that out okay so but
24:53my pricing is in the cost is in the pricing table and I want that in the training table
24:59for every employee I would like to get the cost so I'll go to my training table
25:05and I'll click on new column and here I would like to get the cost like for power bi what is
25:14the cost what is w in this table so I'll just use cost or you can say training cost and I will use
25:22related and I just need to provide the reference of the column where I from where I want this value
25:28and this value is available in my pricing table at the certification cost and this is the column
25:35and you can see how simple it is to get the values so I just got the values from other things
25:40that is how you can use the related ones okay and now since I got that okay and let me add
25:47now let me use that so department wise cost of company-sponsored training so let me add a new
25:54page right click duplicate and this one will be cost analysis part two and let me delete all of
26:01them okay so let's see I want the department let me use a stacked column chart and department is
26:11in the department table training cost into y-axis okay so this is the training cost
26:18the company-sponsored column filtered on this visual and I'll put a check mark on yes
26:25so this is my department wise training cost and if I want to show as a card what is the total cost
26:34incurred by the company on training these employees which are company-sponsored so let's create that
26:40let me add a card okay and I'll just take my cost training cost into the fields
26:48and I will just filter it out company-sponsored filter on this visual so 248k
26:55is actually the total cost for the company so let me change the title
27:02total cost company-sponsored okay so let me centerline semi-bold and let me copy and paste
27:13the format painter okay so one more thing I there is no currency symbol so let me add the
27:21currency symbol as well okay now it's okay so we have done this question as well department
27:30wise cost of company bifurcation of the company and self-sponsored training under each module
27:38so module wise distribution let me make a copy so I don't want department I want module
27:48and in the y-axis
27:52and bifurcation in order to show the bifurcation so I have to use the legend
27:57my status or company-sponsored column into the legend okay so this is the bifurcation
28:04or let me switch on the data legend
28:10okay so this is the way and let me see let me use a bar like this yeah company-sponsored
28:18company-sponsored by module okay and then the last question total number of trainings conducted
28:26under each module along with the total cost for module wise I would like to see
28:32total number of trainings conducted along with the total cost okay so let's in order to show
28:38that I am going to use table so let me show the table and first thing I want
28:45module so I'll drag my module then I want drag my count of employee id next to module
28:53okay and I'll take the count okay so this is how it looking count I'll drag my training cost here
29:03and there I got three information and yeah so this let me increase the font size of this table
29:12okay and I would like to column headers also and I would like to make them more relevant so
29:18module is fine count of id I don't want to use this word I would like to show and click on this
29:25drop down and I'll click on this option rename for this visual and I can type my heading number
29:31of trainings same thing sum of training cost I don't want to show the sum of here so I'll just
29:36go here and modify the heading as just the training cost okay so this is also done okay
29:44and that is how you can complete okay so we have done this one the training this is cost analysis
29:52let me change my heading cost analysis part two okay and cost analysis part one okay so
30:03uh let's do some more formatting okay maybe if you see you can just provide some formatting
30:10for example if you go here to the view so in the view you have these are the themes which are
30:16available okay so you can choose your themes as per which are predefined in power bi so for example
30:23if you choose like this okay so yeah so it will look like this this is called a city park
30:35this is like classic so you can choose themes from this place as well yeah that time that way
30:43you are not required to do the formatting and if you don't want to choose you spend time on
30:51formatting and color selection so you can choose these predefined templates as well and there are
30:58more here as well for example if you click on this drop down so these are some more themes which you
31:03can select okay so I think I'll go with this one okay and this is one way you can select your theme
31:14now let's do one thing let's provide some navigation options to our audience so I'll
31:20add a new page I'll go to insert buttons navigator and I will click on this option
31:27page navigator so automatically page navigator will be added so let me move it to the first place
31:34first page and let me rename this as index okay so this is my page navigator okay and if you would
31:43like to change the shapes so you can change the shapes as per your choice and let me choose
31:50increase the font size okay and color also let me take this one fill let me choose a fill color
32:00okay let me add shadow and the glow okay so you can just so if I just want to see the cost analysis
32:08I can just click on it and it will move to the cost analysis part and on the top you can give
32:12a heading which may be you can insert a logo or the company name as per your choice
32:21okay so that is how you can just complete your training and cost analysis
Recommended
0:14
|
Up next
13:27
24:23
46:16
6:00
10:21
57:51
3:10
6:32
4:31
5:28
2:33
1:53
2:22
3:00
2:12
2:15
3:12
15:14
9:11
6:03
5:06
12:05
Be the first to comment