- 6/1/2025
Power BI_ Analyzing Pharma Sales Data Insights and Visualizations_
Category
📚
LearningTranscript
00:00hello friends so in this video tutorial we will learn about how to create a sales report of
00:21pharmacies so we have a medicines data with us and from this data we have created a chart which
00:28will show a sales of our all the medicines like we have like mo1 m01ae and all other medicine no2be
00:38and we will find out from this report like we have make the maximum sales of no2be medicine
00:46like from this three chart we can see and also we can find out like on which date of the month
00:54sorry on week on which weekdays of uh like we will make the maximum sales like saturday and sunday
01:00we will make more sales and in the year wise we can see in 2016 so this will see give us some more
01:07clue about our say like if i will in the focus mode so it will the year wise and then if we will go
01:15drill down this chart so it will show us according to our quarter wise also
01:20so how to create this report and besides that we have created the percentage of our no2be sales also
01:28like what is the percentage this particular like your medicine have made of the total sales
01:37and this is our like filter so we can see the sales year wise also like if a particular year we want to
01:43see we can do dot also and so this is the best part like what we can do here and here we can clear
01:52selection go ahead and clear selection now we have this report and this is for our particular for the
02:00particular medicine so it is 63 000 sales we have made and this is the chart like in the r wise like if
02:08we will take this not r wise sorry so this is the total sales like here wise we have taken this
02:14and you can do more editing with that for like formatting you can do more and this is our total
02:19sales so it is comparing with this line chart here and this is saturday sunday and this is our wise this
02:26is our uh you can say heat map so we have also created heat map here also and we have created an
02:32heat map in this here also which is according to ours so in which hours we are making the maximum sales
02:39like at 10 o'clock at 11 o'clock on sunday saturday we are making the more sales so this will show us
02:45a very good detailed view of our sales so first of all i will show you the data set so for data set
02:52we have to upload that data set i will show you the data set which is in our csv format which is in e
03:00drive so if you will go to the e drive and i have already downloaded that data set that is our farmer
03:06since and here is our data set so first of all is that data set which i have got but problem with this
03:14data set is like when i will open this data set this is the data set which i have got but date format is
03:22not in a proper format in there like here it is okay like if i will convert it into a date
03:30if i will go date here and convert it into date and press ok so the problem it show is
03:37like in some places it will not change it properly like here it is not able to change it so this is
03:44the problem with this data because here it has taken month first and then your uh what you call that first
03:53of all it have taken month and then it have taken your date so this is the problem so what i will do i will
03:59control that there and control that also this and here i will insert another cell and here i will
04:07create a new date column and here i will put down this is second of jan 2014 it is showing it's like
04:16it's february but it is january then i will put here eight o'clock then i will go here then i will put
04:23again second of jan 2014 and then i will put here nine o'clock and just enter it now we have two date
04:37so similarly it will be on the same day like timing is there and just double click it now if you will
04:43match it data with this data if i will match this the data is the same the only thing is it is taking
04:51your date first and this one is taking your month first so i will just remove this column so we don't
04:56need this column now so now we have a new column with the date column here and here i will put a date
05:02here is our date with time and here it is our uh your medicine names here is a month hours days name
05:11that's all we have so thursday was they watch our date is there so according to that it give us a month name
05:18or even we can take a month name also i will put month
05:23and name here
05:27you can put a formula here also equal to the formula is equal to text
05:34and then i will take this date and from this date i will put your mm and just close this
05:44so it is january it is showing it is january it is february so whichever month is there it will
05:48show all the month throughout the data so it will show all the day like it will show month number it
05:54will show hours and we have created another column with the month name now i will save this
06:02so i will save this data so this data is clean now so after cleaning this data the next thing what i will do
06:09i will close this file and then i will go to my power bi and i will open up a new power bi here
06:16so that we are not mixed up with this data so we will create a report now so it will take few seconds
06:22and you will find power bi screen open and then here we will create a report so it will take few seconds
06:29so just wait for few seconds so it is starting
06:40so now we have a power bi screen with us first of all we have to upload that data so i will go to the
06:46get data and this is in a csv format so i will go to text and then after going to text i will take the
06:55sales are the report which i have saved already so and i will open this
07:03and then next thing what i will do i will put it in a transform so i will just transform it to power
07:10query so that whatever more editing we want to do we can do that so here is the date
07:16and all the things so now i will go transform data
07:18so we are in our power query editor here so i will do more editing with that
07:26so first editing what i will do here is i will just increase the size
07:31and like each have your years and everything month here name there so i will just go there in your month
07:39column like we have a month column year column and month year column that's any or whole number so i
07:46will convert it into i will convert it into a text so it will be replaced at current so i will convert
07:54into text so that we can make a group according to your years here also i will convert it into text
08:02replace and hours also i convert it into text so because we want to grip them according to the
08:10number of hours like this one eight whatever hour we have so this is the data we have so far so we
08:17convert it into text that data and these not need to be converted into text because these we will
08:23calculate what is the sales like these have the sales here so we have to calculate the sales according
08:29to that so all things will be there soon so this data here and now i will go to close and apply
08:38so now we have data loaded in few seconds so just wait so it will take few seconds because
08:44system is not as fast it's not a new system so now you will find sales rd report in our power bi screen
08:58so few seconds it will take and then
09:00i will apply this so now this report is ready
09:16so now this is our sales and these are this column and if you want to see we can go to data and just
09:22check it whatever we have but one more thing we need to do we need to add them so i will go again go to the
09:29power query so one step i haven't like forget so i will just add all the sales of particular day
09:37like on particular hour like eight hour on second of your second of january whatever sale we make we
09:46have to add all the sales so i will select all these columns wherever we have predictions like this one
09:52this one this one and this one and this one so i will just click all this and then i will go to add
09:59column and then i will go to standard and add so what it will do it will add a new column and here i will
10:07write total sales and that is basically per hour per day like this is something like this
10:17per hour per day is the column so i will just put total sales here that's okay so this is total sales
10:23according to r and per day so i will just keep it like total sales only you can do that also
10:32so we have a new column here added
10:34so it will take few seconds and now
10:43we have a new column as a total sales here so this way we can add all the columns we have in the beginning
10:50it is showing zero but later it will show some values also if we'll put it down so there will be some
10:58values like one is there and according to that it keep on increasing like more we go down so this is
11:04our sales of the complete data here so it will add all here we have zero so there therefore we are not
11:12finding anything here but but when we go down we will find some values in most of the cases we say zero here
11:22like maybe there is some problem we have to check
11:30sort descending
11:33i will put sort descending now this is in descending order and if i will put sort ascending
11:39so it's in ascending order like two so now it's okay earlier earlier it was saying some problem now
11:46it's okay so now i will go here in our report and in report first of all i will give some heading here
11:56text and here i will write
11:58medicine
12:08sales report
12:10medicine sales report former
12:13xyz form i will give name
12:17now what i will do i will just put it here
12:21then i will go to the effects
12:23and here i will look some darker color and for this our text i will give white color
12:34and just increase the size to somewhere around
12:38i will just click over this
12:41i will put it 32 here i 32 is more let's see how it look and just
12:46track this area to tilt it till this area and from here also i will track this and put it in the center
12:56so medicine
12:58reports sales report so this is the name now i will also click on this area and i want to increase the size also
13:07so canvas i will go to custom and here i will take somewhere around 1200
13:13and here i will take somewhere around 1800 so i think that much is okay to create a report
13:21so this is the size of our canvas
13:24now i will put some chart here so first of all i will put your matrix and in matrix what i will do
13:32i will put here our hours in rows
13:36and then i will put your week names in column and your total sales in our values so we have a matrix here
13:49now and we just have to increase the size also so i will click over this matrix then i will go to view
13:56then i will go here and from here i will take to current customize current theme
14:01then i will go to text and increase the size to somewhere around i think 18 is okay i will put 18
14:09and apply so now it's increase the size of this so that is more visible just and i will go to here
14:20in this chart and format your visuals and off all the subtotals so i will off all the subtotals and now
14:27we have this chart here just make it a little bit smaller and next thing is i will click over this
14:36go to conditional format i will click over the sum of totals go to conditional background color
14:42conditional format background color and take some darker color here like something like if we have
14:47higher value then we have darker color and if we have lower value then we have a lighter color then i will
14:53press okay so now we have a darker color for more sales and lighter color like we have more sales
14:59during 10 o'clock 11 10 a.m like in the like in the morning then 11 these are the time we have maximum
15:07sales so it will show us the sales according to our uh week wise and our your like your hour wise also so
15:16in which we can which we make the maximum sales i will create another one also so i will create another
15:21matrix here and this time the matrix will be as per your first of all we will take total sales in values
15:30then i will take ours in rows and next i will take months name in column so that's also a matrix here so
15:40i will go there some conditional background color and here i will take the darker color
15:48color and here i will take the lighter color and just press ok so now we have two reports here
15:57according to our month wise also and according to our uh here we also have total so i will go here
16:05and just remove the totals row total will be removed now it's okay so that's the way we can have
16:13like whenever we have maximum sales minimum sales whatever so this is the uh report here for our this
16:21is our you can choose the heat map so this is our heat map next thing we will put a chart so i will go
16:27here i will just make it a little bit down this report here here and that's also
16:34put this also down here that's also it's okay now in this chart what we will do we will take years so i
16:48will put total sales in our x-axis and i will take our tiers in our y-axis so year wise it will show where
16:58we have make the maximum sales here so what it is showing here is you have some double standard there
17:06maybe we have put some i will just click over this clear section so we have selection there that's why
17:15it's showing like this so now we have collection we have to remove the selection next thing is i will
17:23create create your card so this card will be keep uh like here and here i will put the total sales
17:34in this card so now we have this total sales card we can do some editing with this card like color
17:41call out label is okay we need that and what will be the name of the label journal title is off no
17:50problem sum of total sales is there and i will give some coloring also you can do that also this is
17:57basically for call out label number journal effect see like if some color can be given to that
18:05that's okay we can give some color there call out label i will put white color here
18:11call out label call out value i will put white color there also so now we have this total sales here
18:20and this is our heat map this is over here next i will take this tree map so i will put this tree map down
18:28and in this tree map what i will do i will take in values all our
18:33like medicines so whatever medicine we will we are taking i will put all the medicines here
18:43first of all is this second this like we have nine medicines not these names
18:50so according to that it will show the sum of all the medicine and which medicine is making the maximum
18:56sales out of them so i will take all the medicines so now we can know and from this
19:03i will journal i will just off this data chart and now it's look better so like this medicine like
19:11nruby is making the maximum sales with us so this is the best part and here we have year wise sales
19:19and more chart we can make i will just put it down and next start is i will make a funnel here
19:28i will go there and i will click outside take the funnel and in this funnel i will take your month name
19:42and your total sales
19:45so according to your month name it will show you the total sales here so jan october like whichever month
19:52we have the sales and if you will see our last report which we have made so here we have taken this
20:00is our new one so here we have taken weak names also so that thing can be medicine wise like we can also
20:08make this also chart also so whatever way you like which which give you insight of data that is the point
20:17which we have next i will put here and take the line chart and in this line chart what we will take
20:25i will take your like your total sales in y-axis and in x-axis i will take this x then i will take second
20:37medicine and then i will take your third medicine 4 5 6 7 8 so i will take all the medicines here and see
20:50like how this chart will function if i will increase the size so it is giving i will just remove the
20:57headings of the headings of this journal no no this one we don't need that
21:10i we have to give change make some changes there first of all i will go there
21:19not there in here i will have to take total sales
21:23in y-axis in y-axis might be there is some problem
21:32i will just remove all this i think there is some problem with that so i have to remove all this
21:40now now again i will take here your total sales in x-axis and then i will take this in x-axis now it's
21:50much better
21:55and again i will take this sale
22:00and then i will take this no2be and check like this is working or not
22:10and then i will go there see like whether it's working no it's not working the chart
22:15line cluster chart or whatever i will check in the last report which chart i will use for that
22:26so if i will click that okay in x-axis we will take date so date and in secondary axis we have to take
22:33these okay this is the problem here so in x-axis we have to take the date first so first of all we have to take date
22:41so that we can put the date i will click over this and i will take this date here now we have date better
22:48view some of sales is there and then the chart which we have taken there is your
22:55this line chart so i will take the line chart here i will go there and take the line chart
23:00and there in this secondary axis i will take all the medicines
23:07now it's okay so total sales so this is our total sales and now we are comparing it with our other
23:17like whatever we have made so far so it will compare our data with that report also
23:26this is in y axis only sorry it's in the wrong place sorry i have to remove from here y axis not in
23:35the y axis we have to put it in the
23:41just remove them
23:45remove this one and remove this one in this secondary axis i have to take that
23:50here in secondary y axis that's okay and then this one
24:00it's okay okay here we have taken month name so we don't need the month name here okay
24:05okay and then i will take more medicines here month multiples months no
24:19okay i will take more things here like this one and this one
24:27and i will take more
24:28this one
24:34it's again and again i will just remove this
24:37uh it is taking a little bit slow
24:58now this is our report so if we increase the size so this is as per like light chart
25:08and if we will convert it into cluster chart so how it will look let's see so this is as
25:15for the cluster chart like this is our sum of total sales there and this is sales of other medicines so
25:21this case this look better then i will go here in general
25:29and you can even we can we can even remove the legends like if you don't want that's okay so it
25:38will show like which according to like your days quarter month whichever is we are making the sales if
25:44you will go one level down or one level up so it will so according to quarter if you will go more
25:51that's according to the quarter and if you will go that's according to years and in year wise which
25:57medicine is making the maximum so this will show a better view so this way we can create some charts
26:03here next thing is like if we want to create a filter here so what i will do i will just
26:10click over this and from here i will take i will just click outside and then i will click on filter
26:20so we have a filter here and from here i will take an ear filter click over this and we have a year here
26:27then i will go to the format your visuals slicer setting and convert into horizontals so we have six
26:34year so we have a filter of six here with us and if you want to find out the detail of any one year so
26:40it will also give the detail of any one year also and if you want to go and clear filter you go here and
26:47clear filter we have to click over this and it will clear the filter so this is the report more formatting
26:55you can then next is like if you want to know what is sum of this no2be medication is of total sales
27:03so for that like if you want to know the percentage of that so i will go to the new
27:08and new measure and here what we will find out what is the percentage
27:25we will put the name of the no2be of total sales so for that we will put a calculate function here
27:32and then i will put some after putting some i will open this and this is our report so first of all we
27:41have to sum of our you call that no2be medicine so here is so first of all we will sum this medicine
27:50then close this and then we will divide it with some of our total sales and then we will click over
27:59this so now we have a new measure here where we will see what is the percentage of that sale now if
28:07you want to see that we will create a card here i will go there and put a card and this card i will
28:13put this percentage one but it is in point so i will go to format or i will just click over this
28:21click over this measure okay i will click over this measure then from here instead of journal i will
28:34take here percentage so it will convert into percentage so it will automatically convert this
28:41into this is 49.4 percent of total sales like whatever this medicine is doing no2be is 49 percent of
28:48total sales now if you want to make some report on this particular medicine we can do that also
28:54like first of all i will click outside go to table and instead of table i will take this matrix
29:04and i will put here in rows i will put your hours and in columns i will put month name
29:14and no2be is our medicine so i will put here in values now i will click over this then formatting
29:27and make it more colorful hard color like more color so now we will see like in which
29:36like a month we are making the maximum sale of that particular medicine
29:40so this is a report of no2be so we can put a title also there
29:49and here i will write sales
29:55report of no2be because this is our best selling medicine from the list so i will just take this
30:04and create a report on the background color i will put dark then i will put more emphasis like if i will
30:12select this oh sorry select this and from here i will take white color and increase the size make it
30:22smaller
30:35doctors are here now it's okay sales report of no2be so this is about that thing similarly i will take
30:42a bar chart also i will create here i will click click here a bar chart so in x axis i will take this
30:55total sales i will take in y axis and in years i will take this so year wise like what is the condition of
31:05this medicine so like this is our total sale so it is somewhere around in the half each and every month
31:17and here i will put my filter also click here filter and filter i will put according to your years
31:38and then i will go there and slicer setting vertical i will put it horizontal so this is a slicer you can do more formatting i am not doing the formatting in this part
31:51okay so now if you will click over this we can get the sales of a particular year also and if i will clear this then we are in the back position
31:59then we are in the back position so percentage of sales so every year like this uh medicine has outperformed
32:07like 52 percent somewhere 40 but more in more of the cases is generally in 50 percent and in which part
32:13of the day this medicine is like we are selling we have to check it out so the information we have
32:20so this way we have these two report and we can make more like in the last report what i have created
32:24if i will go there similarly like other things also like in the day of the week on other matches we can make
32:35like i will go there in my report and now what i will do i will create one more report here that is your
32:45i will create this fun like if you can create a tree map sorry just change i will just control that
32:55you will not have to change it i will click here and this time i will create your
33:02this funnel and funnel is off from week name and then in values i will take
33:09no2be is our medicine so it will create a funnel saturday we have the maximum sales with this
33:17and next if i will want to create a tree map and that's tree map is for our category i will take this
33:26time weak names not weak name this time i will take month category i will take month and in values i will
33:34again take this no2be medicine so like similarly like it is more in the first month instead of month i
33:45will take month name so that will look more clear so in january february like we have the january december
33:53these are the month like we are making most of the sales for this so we have created a report and from
33:59this report we have find out that no2be is outperformed and like when we will go this chart so we will
34:06find out no2be is the outperformed from this and according to that we will create a report for that
34:12particular medicine also and also we we have created a heat map year wise and month wise similarly we have
34:20created your this chart we can call it funnels so the funnels will show you like in which month we have
34:28make the maximum sales and this will show according to our like whatever is the sales you know like if
34:34you will click over this so this will show us some of total sales and like all the medicines which we
34:42are selling what where where they stand with the total sales if you will increase the size so we have a
34:48different color for different medicines so this medicine outperform here also second number is no2be
34:55so this give us information so you can create this chart so thank you very much for watching this video
Recommended
0:59
|
Up next
0:40
0:15
21:04
5:31
16:56
15:41