This was extremely helpful, covering all essentials for PBI Dashboard. I have never come across with such a detailed and simplified project demo. Moreover nicely explained. Learned a lot from this single project. Thanks a lot.👍
This is the best Masterclass video that I've found because of the amount of tips, tricks and topics covered in one single project. I've learned way more in this 2 hours than watching tons of videos about particular topics, that then I struggle to compile and put in practice. Thank you so much, please keep doing this type of videos (full projects build from cero). I have a couple of doubts Why do we store the values in variables for the formula of the Mom Change % instead of using directly DIVDIDE (Total Sales - Total Sales (prev month), Total Sales (prev month)? I was not able to change the color of the line chart, it only changes the view where all lines are shown. Little suggestion: Please don't accelerate the video when you enhance the visuals. It is useful to see how you make them look from beautiful to even more beautiful.
if you store the values or the results in a variable, it will avoid repetitive calculations or re calculations (reduces the time it takes to load the data)and helps us in debugging the dax code easily and helps us in breaking the complex code into bits so that it provides better readability
Wow, what an amazing project, full of insights and knowledge. It took me nearly half a day to follow along and replicate it, but it was worth every minute. Kudos to you for always being one step ahead. I look forward to more practical projects like this one. Thank you very much, Chandoo. You are an awesome mentor. Salute to you.
Thank you so much, man. I will recreate this project and post it on LinkedIn, tagging you. Not just for the comments, but you've truly been a helping hand so many times in this data analysis world.
I love ❤your content it is straight to the point, you do not waste any time and it is meaningful. I learned a lot in this project. Thank you so much🙏🙏🙏
Will it not be processing heavy and cause lag in terms of performance? As SUMX is an iterative function it will go through each rows and perform action.
@@tashrifmahamudsiddiquee4183 measures are calculated on the fly. the calculations are done in the memory whenever you are going to interact with them in the visuals and they do not occupy any space and store in the data model
When I start power bi .... I think what is var and return in power bi i think I not able perform dax using this var or return but when watch this video it's clear and so simple.... Thanks a lot❤
Functions Learned 1) LastDate 2) FirstDate 3) Calculate 4) Related 5) Divide 6) Edate 7) If 8) Var + Return Features Learned 1) How to create bins 2) How to create parameters/dynamic visual based on slicer 3) How to create Bookmark+Selection switch functionality 4) Learned to use new cards, with reference values (Design option for separate formatting) 5) Gauge with Target setting 6) Learned Zoom slider 7) Image insertion in a table 8) Icon creation in tables, based on performance indicator column made with help of If function 9) Learned to create tooltip Other things that I learned 1) Modeling 2) Observed how multiple tables got picked from same excel file in one go 3) Use of Icons 4) Visually appealing slicers on left panel
instead of creating a calculated column which is going to run under the row context where it is going to evaluate the expression for each row in the table..best option is to create a calculated measure using the sumx and related functions Total Cost = SUMX(Fact_Shipments, Fact_Shipments[Boxes] * RELATED(Dim_Product[Cost per box])) creating the calculated columns will occupy space and are stored in the model. which in turn is going to increase the file size...
Hi Chandoo. your videos are really awesome. Just wanted to know whether your power BI course has 20 hours video lecture as mentioned in comments section(You can get the complete Power BI file, full HD video and more than 20 hours of Power BI lessons in my online Power BI course) or as 9+ hours as mentioned in your Power BI play date. please also can you cover in depth of Power BI service in your course.
Thanks for this lovely comment. Sorry for the description confusion. The course has 20+ lessons, but the total duration is around 10 hours. I have updated the video description now. I am planning to add videos on Power BI service, newer features and SQL integration to the course by the end of 2024. If you enrol, you will automatically access those videos too. See chandoo.org/wp/power-bi-course/ for all the details.
Hi @Chandoo, thanks for the videos, am becoming more better at using power bi, i was able to create the final dashboard you created and added the accent bars for measure selection fields when selected it changes color the problem was doing the same for the KPIs added the accent bars but wudnt be able to sync the two to show same colour on the accent bar when a measure selection field is selected. The other problem was the measure to use for the up by 11% so it can change dynamically when a particular field is selected e.g. sales
What if the sales data are missing in few years,will line chart be continuous or non- continuous?I'm getting continuous line chart instead of non continuous line chart,How to solve this?
The name suggests it's a line chart it will show the trendline even though you don't have some points in between.. if you want to represent just the points try to use other visuals..
Shouldn't the Profit % be DIVIDE([Profit], [Total Costs])? I am confused here and not sure whether I am making a mistake. Could you please provide the answer?
Why is profit % = total profit/total sales .... By formula it should be total profit/total costs Profit % = (SP-CP)/CP Right ?? Please correct me if I'm wrong
Hi @Chandoo, I tried a lot, but not able to create 'Up by ...' visual based on slicer parameter. Though individually for sales, profit etc, I was able to create % change using firstdate function. Can you let me know how to create 'Up By...' dynamically?
Sir a very humble request. Please start using different data sets for useful long videos like this so we can benefit from looking at different scenarios, like some time HR data, Biotechnology data set, Data related to Telecom, A pharmacy, Logistics based company, Housing, Government related, and likewise or rather pick a data set which is challenging, broke and needs efforts to clean and transform...Barfoughny, Jehu and Juices are not helping anymore. Sir please try to understand the need of people from different backgrounds.
Hi sir in measure i have applied Latest MoM Sales Change % = var 1d=[Latest Date] var this_month_sales = [total sales latest month] var prev_month_sales = CALCULATE([Total Sales], 'calendar'[Start of Month] = EDATE(1d-1) return divide(this_month_sales-prev_month_sales,prev_month_sales) it is showing error
Hello Mr. Chandoo, Request you to please read this and suggest an solution; I am having a large data set of sales register of last 4 year containing data of invoice wise SKU wise sales with buyer code, buyer name, gross and net values, taxes etc etc. To give an idea these excel data files have data till like 7-8 lakh rows and columns going up to DA and even further sometimes. At times it needs to be updated as per various management reporting requirement in terms of product category grouping etc. I tried to connect it with power bi desktop app, however it is taking too long. How to resolve that ??
Looks like you are using a slower computer (processor / memory / both). I suggest saving the file as CSV (won't work if it has formulas) and trying again. If that is also slow, test it on a faster computer with more memory and resources.
It depends on the profile. Most jobs require one of the two tools. So start learning. All the best. Here is my 2024 recommendation and road map for becoming a data analyst - ruclips.net/video/R3bZ0LLv3Fk/видео.html
It has been around for a while. I suggest checking again. If the parameter is a dimension, then it should go to the dimension axis (for example, on a column / line chart, this would be x axis). Likewise, if the parameter is for measures, then it should go to the value axis.
Caledar table is a best practice when it comes to serious BI reporting. It will help you deal with multiple date columns, simplifies date intelligence calculations (such as MoM / YTD) and keeps your model fast. The built-in data columns can be slow, tedious and even dangerous when dealing with large fact tables.
Adding the line requires creating a measure to know which field parameter is selected and then using conditional formatting to check if the measure selected is same as the measure shown in the card. Then change the color.
They are available in the description. Here again: Github link: github.com/chandoo-org/Power-BI/blob/main/ac-sample-data.xlsx Mirror link: chandoo.org/wp/wp-content/uploads/2024/07/ac-sample-data.xlsx
Thank you Chandoo, you are helping a lot of people to be honest, this is good , you are saving our jobs at workplaces out there
I echo this..
This was extremely helpful, covering all essentials for PBI Dashboard. I have never come across with such a detailed and simplified project demo. Moreover nicely explained. Learned a lot from this single project. Thanks a lot.👍
This is the best Masterclass video that I've found because of the amount of tips, tricks and topics covered in one single project. I've learned way more in this 2 hours than watching tons of videos about particular topics, that then I struggle to compile and put in practice.
Thank you so much, please keep doing this type of videos (full projects build from cero).
I have a couple of doubts
Why do we store the values in variables for the formula of the Mom Change % instead of using directly DIVDIDE (Total Sales - Total Sales (prev month), Total Sales (prev month)?
I was not able to change the color of the line chart, it only changes the view where all lines are shown.
Little suggestion: Please don't accelerate the video when you enhance the visuals. It is useful to see how you make them look from beautiful to even more beautiful.
if you store the values or the results in a variable, it will avoid repetitive calculations or re calculations (reduces the time it takes to load the data)and helps us in debugging the dax code easily and helps us in breaking the complex code into bits so that it provides better readability
Wow, what an amazing project, full of insights and knowledge. It took me nearly half a day to follow along and replicate it, but it was worth every minute. Kudos to you for always being one step ahead. I look forward to more practical projects like this one.
Thank you very much, Chandoo. You are an awesome mentor. Salute to you.
Bro where are the logo links given which are used in the video in the headers?
Thank you so much, man. I will recreate this project and post it on LinkedIn, tagging you. Not just for the comments, but you've truly been a helping hand so many times in this data analysis world.
Thank you Chandoo, you are helping a lot of people to learn on PBI.
I love ❤your content it is straight to the point, you do not waste any time and it is meaningful. I learned a lot in this project. Thank you so much🙏🙏🙏
I think it's better to compute the Total Costs this way ;)
Total Costs =
SUMX(
shipments,
shipments[Boxes] *
RELATED(
products[Cost per box]
)
)
Will it not be processing heavy and cause lag in terms of performance? As SUMX is an iterative function it will go through each rows and perform action.
@@tashrifmahamudsiddiquee4183 measures are calculated on the fly. the calculations are done in the memory whenever you are going to interact with them in the visuals and they do not occupy any space and store in the data model
I love the birds sound at the beginning of sections.😊
I started creating dashboard along with u and it's amazing
Thank you so much chandoo
I always love your lessons
I wish I could send you the screenshot of what I replicated
Chandoo Sir, You are Awesome, I'm following you from long time starting from ms excel. You're always an inspiration for all of us 🙏🏻🌹
When I start power bi .... I think what is var and return in power bi i think I not able perform dax using this var or return but when watch this video it's clear and so simple....
Thanks a lot❤
Thank you chandoo anna , you covered many topics in just this one single project. Very helpful for freshers .. Thank you
Functions Learned
1) LastDate
2) FirstDate
3) Calculate
4) Related
5) Divide
6) Edate
7) If
8) Var + Return
Features Learned
1) How to create bins
2) How to create parameters/dynamic visual based on slicer
3) How to create Bookmark+Selection switch functionality
4) Learned to use new cards, with reference values (Design option for separate formatting)
5) Gauge with Target setting
6) Learned Zoom slider
7) Image insertion in a table
8) Icon creation in tables, based on performance indicator column made with help of If function
9) Learned to create tooltip
Other things that I learned
1) Modeling
2) Observed how multiple tables got picked from same excel file in one go
3) Use of Icons
4) Visually appealing slicers on left panel
Thank you for making this. If I could give more than 1 likes, I wish. I have learned so much from this. thanks Chandoo. A Brilliant job!
Sometimes I forget I have you and what a genius you are !!!
Excellent..🎉.. Great explanation.. thanks for sharing....
Thank you Chandoo, I keep waiting for all your video, I knew your hair are not like its showing in your account picture🤪
Thanks Chandoo, It was awesome project. Learned a lot. You are a good teacher :)
I cannot find the images and logo file
Hi Chandoo
Could you create a video where we could learn when to combine filter with ALL function while creating measures !❤
Excellent Tutorial. Definitely A+
Awesome Choc... project! Thank You Chandoo!
Thank you so much for uploading sir ... Please put this video in telugu also 🙏 it would be helpful a lot
Hello Chandoo, thankyou so much for the video. Just wanted to ask if the Profit% should be tota profit/Total cost and not total sales?
instead of creating a calculated column which is going to run under the row context where it is going to evaluate the expression for each row in the table..best option is to create a calculated measure using the sumx and related functions
Total Cost = SUMX(Fact_Shipments, Fact_Shipments[Boxes] * RELATED(Dim_Product[Cost per box]))
creating the calculated columns will occupy space and are stored in the model. which in turn is going to increase the file size...
Hi Chandoo. your videos are really awesome. Just wanted to know whether your power BI course has 20 hours video lecture as mentioned in comments section(You can get the complete Power BI file, full HD video and more than 20 hours of Power BI lessons in my online Power BI course) or as 9+ hours as mentioned in your Power BI play date. please also can you cover in depth of Power BI service in your course.
Thanks for this lovely comment. Sorry for the description confusion. The course has 20+ lessons, but the total duration is around 10 hours. I have updated the video description now. I am planning to add videos on Power BI service, newer features and SQL integration to the course by the end of 2024. If you enrol, you will automatically access those videos too.
See chandoo.org/wp/power-bi-course/ for all the details.
@@chandoo_ Thank you.
1:52:00 We can also add GIFs. Using Html also we can do lot more!
Great Explanation! Insightful Dashboard
Hi If not getting table and model under data tab please enable preview features under global in file and option settings
Hey Chandoo, I don't see the icons file in the description.
Great video btw!
Thank you so much ♥ your work is exceptionally good
Thanks so much for sharing this video.
GOOD MASTER CLASS ON POWER BI BROTHER !
Thank you so much for such details version of PBI report
Thank You Sir. God Bless You
Informative and Descriptive as always..
2 hours took me 6 days. Lots of stuff.
Thanks Chandoo! It would be great if you give the pbix file of this report.
Hi @Chandoo, thanks for the videos, am becoming more better at using power bi, i was able to create the final dashboard you created and added the accent bars for measure selection fields when selected it changes color the problem was doing the same for the KPIs added the accent bars but wudnt be able to sync the two to show same colour on the accent bar when a measure selection field is selected. The other problem was the measure to use for the up by 11% so it can change dynamically when a particular field is selected e.g. sales
Awesome project. can't thank enough for proving such a quality content. Can someone help me find the icons used in the project for creating dashboard
Exellent Chandoo really a fantastic work
Thank you so much 😀
Another great video, thank you Chandoo. I need a formula that adds the digits of any number I type in a cell. Is it possible?
very informative thanks for teaching us
Excellent Chandoo!
Thanks Chandoo for the useful information
Great video, please also provide logo in the description.
You're the best. Thank you
Thanks Sir ❤🙏🙏🙏
Please make a dashboard for sourcing side. Your ideas are good.
What if the sales data are missing in few years,will line chart be continuous or non- continuous?I'm getting continuous line chart instead of non continuous line chart,How to solve this?
The name suggests it's a line chart it will show the trendline even though you don't have some points in between.. if you want to represent just the points try to use other visuals..
Shouldn't the Profit % be DIVIDE([Profit], [Total Costs])? I am confused here and not sure whether I am making a mistake. Could you please provide the answer?
Profit percentage should be Divide([Total Profit], [Total Cost])
Thanks Chandoo garu.
Why is profit % = total profit/total sales .... By formula it should be total profit/total costs
Profit % = (SP-CP)/CP
Right ??
Please correct me if I'm wrong
Sir we were waiting in telugu
Hi @Chandoo,
I tried a lot, but not able to create 'Up by ...' visual based on slicer parameter. Though individually for sales, profit etc, I was able to create % change using firstdate function.
Can you let me know how to create 'Up By...' dynamically?
Muje sikhna hai so it will be useful for my accounting field
Excellent. ❤
Sir a very humble request. Please start using different data sets for useful long videos like this so we can benefit from looking at different scenarios, like some time HR data, Biotechnology data set, Data related to Telecom, A pharmacy, Logistics based company, Housing, Government related, and likewise or rather pick a data set which is challenging, broke and needs efforts to clean and transform...Barfoughny, Jehu and Juices are not helping anymore. Sir please try to understand the need of people from different backgrounds.
SIR, not getting twitter and any mail from Your side
God Bless You 🙏🙏🙏
really helpful
Thank you chandoo
@00:17:43 - Using the Calendar table to make "time intelligence" measures
Thank you sir
Hi sir in measure i have applied Latest MoM Sales Change % =
var 1d=[Latest Date]
var this_month_sales = [total sales latest month]
var prev_month_sales = CALCULATE([Total Sales], 'calendar'[Start of Month] = EDATE(1d-1)
return
divide(this_month_sales-prev_month_sales,prev_month_sales) it is showing error
Hello Mr. Chandoo,
Request you to please read this and suggest an solution;
I am having a large data set of sales register of last 4 year containing data of invoice wise SKU wise sales with buyer code, buyer name, gross and net values, taxes etc etc. To give an idea these excel data files have data till like 7-8 lakh rows and columns going up to DA and even further sometimes.
At times it needs to be updated as per various management reporting requirement in terms of product category grouping etc.
I tried to connect it with power bi desktop app, however it is taking too long. How to resolve that ??
Looks like you are using a slower computer (processor / memory / both). I suggest saving the file as CSV (won't work if it has formulas) and trying again. If that is also slow, test it on a faster computer with more memory and resources.
awesome👌👍
sir i understood power bi but some DAX i am struggling to get it like big formulas
Sir I have one question
To become Data Analyts did we have to learn full python and Excel?
It depends on the profile. Most jobs require one of the two tools. So start learning. All the best.
Here is my 2024 recommendation and road map for becoming a data analyst - ruclips.net/video/R3bZ0LLv3Fk/видео.html
Thank You @Chandoo
Where have you given the links for the logo?
I am not able to find the link to download sample file
Thanks
Pbix file?
Thank you..
@00:27:22 - Wireframe design for our dashboard
Can you pls share a video how to link Google sheet to Power Bi & update regularly?
Thanks bro
I think the parameter feature dosent work in my version of power bi because when I added it to my chart on the y axis, the chart was blank
It has been around for a while. I suggest checking again. If the parameter is a dimension, then it should go to the dimension axis (for example, on a column / line chart, this would be x axis). Likewise, if the parameter is for measures, then it should go to the value axis.
Sir Please provide the pbix file also
Why didn’t you divide the profit by total costs to get profit percentage
May I ask why we need a separate calendar table if we already have a date column in the fact table?
Caledar table is a best practice when it comes to serious BI reporting. It will help you deal with multiple date columns, simplifies date intelligence calculations (such as MoM / YTD) and keeps your model fast. The built-in data columns can be slow, tedious and even dangerous when dealing with large fact tables.
Hello sir , i cant find files used in videos like .png etc except .csv , please share those .. Thank you
Thanq sir
Icons were not uploaded to git
Q. can we do MOM and YOY changes by quick measures function ?
You can try. I think in this case, it might work.
we can calculate Costs using a measure-
Total Costs=
sumx (
shipment,
shipment[boxes]*related (products[cost per box])
)
Really informative video.
Loved it!
Thank you for making such quality contents @Chandoo
We want more of such useful videos!
Seems like calculated groups doesn’t work with reference label
They don't. Another option is to use DAX Query View (or Tabular Editor) to bulk create the measures.
First 15m viewers😊
We don't get the excel data 😢
can Chandoo/someone please explain how to get that line when slicer/parameter is selected on time 1.46?
Adding the line requires creating a measure to know which field parameter is selected and then using conditional formatting to check if the measure selected is same as the measure shown in the card. Then change the color.
Shall I do this to showcase in my resumae
Sure. As long as you credit the source.
my vote to version 2
Hi
Could you pls share the data file as I do not see in description as suggested in the video?
They are available in the description. Here again:
Github link: github.com/chandoo-org/Power-BI/blob/main/ac-sample-data.xlsx
Mirror link: chandoo.org/wp/wp-content/uploads/2024/07/ac-sample-data.xlsx
Sir data file not found
Please give the links for the logo's
instead of donut can we use map there ?
We can. As we have only 6 countries that are geographically spread apart, the map will have large amount of blank space.
can you share the data link pls