Power BI Project For Beginners | Sales Insights Data Analysis Project - 5 - Build Dashboard
Вставка
- Опубліковано 15 вер 2024
- In this video, we will do some more data cleaning and build a powerful dashboard that can help us generate sales insights on the Atliq hardware business.
To download all the resource files: codebasics.io/...
(You can find the SQL queries, setup instructions, and Power BI formula in the Readme file)
Previous video: • Power BI Project For B...
Next video: • Power BI Project For B...
How to learn data analyst skills for free: • Skills required for da...
Machine learning tutorial playlist: • Machine Learning Tutor...
Entire playlist for this project: • Power BI Project For B...
Special thanks to my friend, Hemanand Vadivel ( / hemanand-vadivel-0b34aab5 ) who is an experienced data analyst manager working for a company in UK. He has a major contribution in this project.
Basics of DAX & Data Modelling are the two fundamental technical skills required to author Power BI reports - There are the free courses I recommend from SQLBI.com
DAX: www.sqlbi.com/...
Data Modelling: www.sqlbi.com/...
#️⃣ Social Media #️⃣
🔗 Discord: / discord
📸 Dhaval's Personal Instagram: / dhavalsays
📸 Instagram: / codebasicshub
🔊 Facebook: / codebasicshub
📝 Linkedin (Personal): / dhavalsays
📝 Linkedin (Codebasics): / codebasics
📱 Twitter: / codebasicshub
🔗 Patreon: www.patreon.co...
The sequel of this playlist is available in form of a video course here codebasics.io/courses/power-bi-data-analysis-with-end-to-end-project If you like this playlist you would absolutely love the course as it is 50X better in terms of quality and content.
While I add the year and date and then i tried to click on the block to get the quantity and revenue i was getting the blank as output in the screen, can you help me on this!
bcoz your model is wrongly mapped. connect date with order date. it will resolve the issue
@@vinayakh3264
I have same issue and not resolved by your solutions.
@@vinayakh3264 thanks correct solution
@@vinayakh3264 market_name by revenue is not working. its blank even after building relationships
i think this is the right place for every beginner who have became a data anyalyst..I'm one of them.....last 3 days i have working on this project,,eairlier get some issues regarding power bi ,because when i watch this videos it is a 2 yrs later and now power bi is updated so some little issues i have get but now after some time i resolve it successfully.and finally i have make a sales insights dashboard ..thank u so much sir you have done a great job for us..💯💯
Hi Pravin. Could you help me with one issue? At 28:30 when Dhawal sir is updating the first bar chart's y-axis from "customer_name" to "market_name", it is showing the same values for all the bars of the chart in my case. I have recently started this project and also have the updated version of Power BI like you. So, did you face the same issue? Please help me out here
I never understood Power BI better than this. Thank you so much. My only regret is not starting with this playlist first.
yep me too
Sir we generated a new column from sales_amount which is norm_sales_amount because it was having 2 entries in USD and rest in INR and we created a measure "Revenue" if we have used norm_sales_amount column in calculating the revenue measure then we would obtained some different value and what I think would have been correct one although it will not make a huge difference in the revenue.
On an end note
the power bi series was fabulous
Sure I am going incorporate this feedback in upcoming video
While using norm_sales_amount, I have noticed it throws the error in the measure column. After small R&D found error is coming due to the data type of this column. We need to change the data type to decimal by clicking 1.2 in the column header in Transform Data window.
@@RajaKumar-hg9wl thanks budd
best teacher on youtube which avail every thing for analysis the data well done sir
Pandas is very powerful when it comes to data cleaning so I would love to clean my data in pandas rather than screwed up your head in powerBI. It is better to clean your data in pandas and import each and everything in your PowerBI that will save you lot of time
If you are using pandas without PBI, there is one disadvantage in this case you need to connect to Database using some connector, Extract the data perform transformations and load the files into PBI, But look at the complexity in this case there are 5 tables, moreover this database may update, So power Query Will automate this but with pandas its Kinda difficult.
Yeah, indeed pandas is powerful. I have intermediate level proficiency with pandas, but there you can manipulate data so well and easily. Plus, I think with Python the process can be automated and can be connected with PBI
💯 Exactly!!! CLEAN USING PANDAS AND IMPORT TO POWER BI 🔥
@@harshpriyam7280 what if I clean all this by using mysql? Is it possible
0:30 #1
5:25 #2
9:50 #3
10:55 completing data cleaning & ETL
11:25 - 37:25 building dashboard
38:00 - 40:00 interaction with dashboard
Hi Dawal,
After setting 'set as default schema' , SELECT count(*) FROM transactions WHERE transaction.currency = 'INR
' ;
can also be written as SELECT count(*) FROM transactions WHERE currency = 'INR
' ;
give the same output.
in top 5 product matrix , you have selected wrong filed . Instead of selecting from sales products we should select it from transaction table . Becuase if we search in SQL , their are 279 distinct product code in sales products table where in transaction table 339. So , if we drag product code from products tables (339-279 = 57) 57 rows will be considered as blank while adding total revenue.
Thank You very much for the comment. It was very helpful.
Great video, I think the reason why the top product says 'Blank' is because of a decrepancy in the star schema. I tried pulling the product_code column from the sales transactions table (instead of the sales products table) and it works just fine. Also, wanted to point out that you did not used the normalized sales amount and used the sales amount, which takes in USD.
it's so fun to see you in your own element. :D
Also thank you for teaching this project.
I had query in the third video about
but now it got solved. Thank you for that
☺️👍
Thank you very much sir. Sir we need more this kind of businesses analysis video. Thank you again. From Bangladesh
Will try to upload more. Thanks. 😊
In 5th part, when I add a slicer it gives me a blank value. Why it is giving blank??
Uh got a solution ?
To remove duplicated currencies due to extra characters, just right click select transform and click on clean, it'll automatically remove extra characters.
yeah, a lot simpler, dont know why he took the long route here specially since this is a beginner project.
Hey Dhawal!! This type of issue we face in every day work because sometimes data gets punched in system wrongly and this issue occurs. We need to check the same for other important attributes such as product category and product details.
Its great learning from you!!!
loved this project series so far and your teaching sir..
it is becoming more interesting to learn powerbi after seeing your videos
This is really very well explained. Each step in detailed without a hurry. I enjoyed creating this dashboard alongside you. Thank you so much, Sir!
Well Explained!! Love this project series and learnt many new things from this series. Thank you.
Nice explain sir . one should watch this series to understand actual work of data analyst 😊
Feel like everything gonaa smooth like amul butter.
U've unique ability to connect like this much.
Ha ha.. i love amul butter. Thanks for the comment Sumit :)
You're doing such a good things sir than you for your tutorial it helped me to understand more about power bi
Keep doing more useful videos sir
Thanks from the bottom of heart ❣️❣️
👍😊
Thank you for your video. this is my first time in power bi.
I am enjoying this video so much because you are so natural and real and no this is not making me sleepy at all , thank you so much
You did a great job we need to make special procedure how to find blank,how to find duplicate in sql and remove the value in sql itself by data engineer then only we got good insights
The issue of 2 seperate entries of INR and USD could be resolved in the power editor of PowerBI as well :
(If we want to merge and have single entries for INR and USD then follow the below steps)
The respective steps are :
1. Extract text before delimiter - Input R for INR and D for USD
After the first step all the entries would be either IN or US
2. Replace values - Replace IN with INR and US with USD
Your videos are very helpful. Thank you for the roadmap. I am following it and able to perform all the tasks as per your instructions...!!
Thanks a ton...!! 🙏🙏
The REVENUE measure should be SUM(norm_sales_amount) and not SUM(sales_amount). We made a new column (norm_sales_amount) to convert the USD values to INR, why aren't we using it? Using just the sales_amount will give wrong results. Please check and confirm.
Yes. I agree.
This is the most fun I've had on any tutorial🤣😂
Thankyou!! I didn't make as many best friends in my life as I made in this video haha
¨Dear friends, remember, data will never lie!¨ 😂 thanks man for all this knowledge!
Thanks, you provided a real case, and showed how to solve the problems.
Hi @codebasics for calculating revenue at 12:41 should we use the normalized amount instead of sale amount as it has all the amounts in INR?
I too agree, because norm sale amount is the amount that has amount covered from the USD to inr.
In the matrix of top 5 products we have to consider the product code field from sales_transactions table instead of Product table.Its showing blank because we have filtered some data to adjust the duplicate currencies.If we cross verify with query in the database it can be seen that the database has no missing values so its clear that some thing is wrong from the dashboard end.
Thank you arijeet, even I too also wondered about the same issue. your comment helped to clarify my doubt.
thanku arijeet. i also dont like this blank
I m binge-watching this playlist, just awesome knowledge
Glad you enjoy it Haroon!
Just completed up to this .I love it ❤️..U r doing a great job🙌.and Btw tell bhavin patel not to watch the reports in mobile while driving 😅...
ha ha.. good tip. sure I will inform him :)
Thank you for this series from India
One correction for Revenue we have to take norm_sales_amount instead of sales amount
hey i have done that but it's not showing the results. Can you please help?
When I'm dragging out the year from sales date and applying the slicer it shows blank on the list and when I'm clicking on the year it's not showing the corossponding revenue and sales qty what should I do???
have also faced same problem .but i resolved it. Just watch previous tutorial carefully that how dhawal sir made relationship with different table while building a model @ time stamp 5:30
Whenever i add year to the dashboard , along with years i get blank button also just like years. when ever i click on any year my revenue and sales shows blank instead of showing the value.
This is a piece of art
It took me half day to figure out that tables should have relations for slicer to work. missed this point in part 4. now learned it after wasting half of my day. 😂🤭
Loved this Project Series
Learning like a night owl 🦉 Thank you.
While changing the "revenue by customer' bar chart, all the bars show the same value as soon as I replace the Y-axis from customer_name to market_name. X-axis is same as before i.e, 'revenue'. I can't understand why that is happening. If anyone got any solution plz help
Hi Dhawal, After adding slicer in the report when I select any year data in other reports isn't changing. Do you know why?
You first need to relate [order_date] from sales_transactions table to [date] in sales_date in the model schema.
@@Tiago092 how to do that
just establish relationship between Order_date from sales transactions and date in sales date in Model view and everything will be fine. For better understanding please watch previous lecture of Dhaval sir on 5.30
thank you for great knowledge, only thing I wanted to add is instead of filtering out data with INR and USD we would have simply used if else to convert USD#(cr) to USD and INR#(cr) to INR. please correct me if I am wrong
Excellent learning so far, It would be great if you could do couple of more complex power BI enlightening session, loads of thank & greatly appreciate your efforts & time.
Really love your projects series.
Wonderful teaching and project. keep it up
Why did you use the sales amount and not the revised sales amount where we converted the currency into INR?
HI Dhawal,
Thanks for your efforts.
Also we can create view to sort the issue concerning currency.
Bellow is my approach towards it.
create view vw_transactions as
select product_code,customer_code,market_code,order_date,sales_qty,sales_amount,
case
when currency='INR' then 'INR'
when currency='INR
' then 'INR'
when currency='USD' then 'USD'
when currency='USD
' then 'USD'
end currency
from transactions
where sales_amount >0
Very helpful video. Loved your content.
Just one question .. Why is the top 5 products initial product code is blank.
I could not find any transaction in SQL workbench which does not have a product code
@12:12 🤔 revenue is the sum(norm_sales_amount), isn't it?
To avoid any future problems with currency amount we could use
CURRENCY_AMOUNT>0
instead of filter 0 and -1 or in future any other negative value
Sometime we record sales amount Zero, when we give items as giveaway or sales Promotion, but inventory should be out. so we need to keep this record.
While I add the year and date and then i tried to click on the block to get the quantity and revenue i was getting the blank as output in the screen, can someone help me on this!
SAME when clicking on year block not getting revenue and sale_quantity of that year.someone help
@@kamalgopalsingh244 Hi Kamal, Issue is solved.
The issue is you are not relating properly of concern date/something which you are not getting data properly.
Got to data tab and check the Date is properly connected to the central table.If not make connection proper and then try again.
If yet you didn't find can u just go to the previous videos where Stat schema e.t.c are explained and watch sec to sec. you can have better clarity.
Sir, my power bi is giving error for the first measure . Revenue. It says there is string type in the column new_ sales_amount. That's why can't Sum . ...!!
By the way we have to use normalised sales amount right? Why are we using old sales_amount..??
Hello Sir,
we are filtering INR and INR/r. INR/r is having maximum observations hence keeping those rows and filtering out the INR header which are having 279 rows.
cannot we change INR into INR/r because there is no error in the amount and filtering out those values may lead to fewer revenue numbers?
When I am trying this project at 21.24 min you are showing vertical list of year, but i am also getting blank option along with year
Please can you hep me
Hello all I am following this playlist and doing this project along I am struck in a place....and need help
when I use slicer for year and when i click on a particular year the values are remaining unchanged....Tried my best and cannot figure it out
Bro, same error.... Did you find any solution?
Hello Brother, You are doing great job. I really loved your Power BI series. Can we write simple SQL query to check revenue in 2020? I have one suggestion and it gives me same answer. The query is 'SELECT SUM(sales_amount) FROM transactions WHERE EXTRACT(YEAR FROM order_date) = 2020 and currency = 'INR
' or currency = 'USD
';' Welcome for suggestions and also let me know if I am making any mistake here.
I runned your qwery and it worked pretty fine and also checked the value in the dashboard it matches the same
Thanks a lot. Awesome learning and very nicely explained.
I checked in SQL and query there are no blanks value in products_code in product table
the line chart show a straight line, also the total revenue and sales doesn't change when I change the year o month choice from the slicer what's the problem?
You have to fix the schema under model section(third option left side)
i had similar issue once check your star model by connecting necessary this may help you.
@@harikavootukuri1313 it worked thanks harika
Huge thanks...love your all series of videos...
Hlw sir,
i can't get the visuals as yours as i change the years in slicer they show " Blank" valued .
sir can you please assist me how can i get it properly .
waiting for your positive reply 🙂
sir in my power bi i am not getting same options as yours. ex - in data labeling option i am not able to change billions to millions
awesome 👍👍 please bring more learning projects like this
I am happy this was helpful to you.
Hello, Can somebody please tell that after making a card and graph represting revenue and sales quantity , when I am using slicer of year ,why it is not working and showing any change at tables..
can we convert INR to USD then finally USD to INR to avoid the old rate of usd to inr conversion thing like in 2017 we may have different conversion rate
yes so ideally instead of using fixed rate you will call some api that can give you latest exchange rate. Or better is to retrieve exchange rate at the time when transaction happened. That will give you most accurate result
Hi, currently I am working on this project now but I am facing an issue while creating a drop down for year column which is in sales date table. The year column is not mapping with the data in report view.
Sir when I am adding the year table it's is also showing blank option and when I select blank column then only the details is being shown or else it is showing blank everywhere
I have also faced same problem .but i resolved it. Just watch previous tutorial carefully that how dhawal sir made relationship with different table while building a model @ time stamp 5:30
When I select the year on the slicer, the revenue and quatity cards are showing (blank). Any reason why this is happening?
Hi Sir, got n error with year 2020 data as I am getting blank fields when clicking on 2020 year or months. Anyone can help.. would be highly appreciated.
I am also getting the same error. Please help me out
if i select the year slicer no data is visible what to do please help me
at 35:30 i was literally sleeping when you said i hope you guys are not sleeping xD
I have a question, since we created a new col which contains currency amount converted to INR ,then why we did not use the new col for calculation of sales_amount?
Hey Hi ..When I am selecting any of the Year from slicer like 2017,2018,..Its showing the "Blank " in the rest of the visuals ... same thing is happening with Cy_Date
thank you sir.....i practiced.....it, its cooll.......when i got some order will let you know.....:)
I get blank on my slicer for year, what could be the problem?
Hi, I am not able to make my slicer responsive, can you please help me with it???
21:41 Here everytime I am putting sales date year into the slicer, another (blank) button is being created. When I am clicking on the (blank) button, the previous values, i.e., the cumulative values are being shown, and when I am clicking on the other 4 years, the 4 visuals just say BLANK. I just can't understand why this is happening. Can someone help me out please?
same for me...can someone help
I am still confused about the Cy_date column in date table can someone explain me ?
When I change the customer_name field to market_name in the Y axis I get only one entry for blank. none of the cities are being displayed. Any help?
Just a simple suggestion, we can also fix the base data once we get to know these are the errors.
what's the harm in that.
I also build my dashboard with few more modification it's great
hey guys when uploaded the date in sheet its not changing the values when iam clicking the year
sir, how to handle the blanks in slicer
Sir, there has to be a query to extract unique data type so that we can quickly figure out the odd man out...
Right??
Loving it so far.
Glad you liked it mallu
@@codebasics Hope you would add more videos on sql and powerbi
can some one help me, whenever im preparing the report the value of all the revenue is comming as same ie, 90billion
Year buttons are not working , records are not changing when I'm selecting the year can somebody help me on this ?
I am facing a problem with the slicer problem when I click 2020 it does not show 2020 data means not reflect revenue or qty I tried it so many times also can you please tell me how to remove it I have tried everything
I saw a relationship there was not any relationship date column actually I am a beginner now what to do next can anybody help me
When I am adding slicer for months it's not showing me the unique values instead it's repeating Jan 19 Jan 19 in this format
Ohh no, I don't understand. How can you remove the records having INR or USD just like that. Even if there is one transition it might be important. It would be more wise if we just replaced the value by using replace value option. Let me know if I am wrong.
Thank you very much Dhawal.. !!
I've some points in my mind,
As we've not used norm_sales_amount anywhere while building the dashboard, then what is the point of creating it;
And I've tried to use that column in place of sales_amount in revenue formula, It didn't work as expected, can you please check....
Second thing is that we can't say the sales are declining from 2019 to 2020; as we're not having complete 2020 data..
i cannot find orientation option in my power bi as shown at 21:54
hi there, Thanks for sharing this amazing series where we can learn professional practices as well. Always looking for something like that.
I want to ask something. While building the dashboard I am trying to show the total revenue by product type through a donut chart. we have two distinct values in product type. one is Distribution and one is Own brand. Now the thing is when I put product type in the chart it shows two distinct values but when I drag the total revenue in it. it shows the third option which is blank and it covers the 47percent of the revenue. Is it that the transaction table has the sales of products that are not in the products tables?
Can someone help me out with this?
Can someone kindly assist me. When adding Year & Dates as slicers into PowerBI, these don't seem to be related to the Measures. I've followed everything to the tea
Thank you sir. it is very helpful
Keep it up
it shows blank and not being able to get rid of it ,tried everything by seeing other yt videos ,but nothing seems to be working ,can someone please help regarding this
I am also facing the same issue
did you able to solve it?
@@Santosh-lz3zz no not yet
@@souravmohanty6656 if it was returning blank value when you are applying slicer create another date table it worked for me