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.
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
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🙏🙏🙏
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.
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
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❤
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, 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
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.
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.
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?
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?
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
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..
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 - ua-cam.com/video/R3bZ0LLv3Fk/v-deo.html
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
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.
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.
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.
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..
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?
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
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🙏🙏🙏
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 the birds sound at the beginning of sections.😊
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 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 🙏🏻🌹
Thank you chandoo anna , you covered many topics in just this one single project. Very helpful for freshers .. Thank you
Sometimes I forget I have you and what a genius you are !!!
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!
Thanks Chandoo, It was awesome project. Learned a lot. You are a good teacher :)
Excellent..🎉.. Great explanation.. thanks for sharing....
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
Awesome Choc... project! Thank You Chandoo!
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, I keep waiting for all your video, I knew your hair are not like its showing in your account picture🤪
Great Explanation! Insightful Dashboard
Thank you so much ♥ your work is exceptionally good
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...
Excellent Tutorial. Definitely A+
Thank you so much for uploading sir ... Please put this video in telugu also 🙏 it would be helpful a lot
Thanks so much for sharing this video.
Informative and Descriptive as always..
Thank you so much for such details version of PBI report
Thank You Sir. God Bless You
very informative thanks for teaching us
1:52:00 We can also add GIFs. Using Html also we can do lot more!
GOOD MASTER CLASS ON POWER BI BROTHER !
Thank you so much for sharing this video.
Hi Chandoo
Could you create a video where we could learn when to combine filter with ALL function while creating measures !❤
You're the best. Thank you
Thanks Chandoo for the useful information
Exellent Chandoo really a fantastic work
Thank you so much 😀
Excellent Chandoo!
@00:17:43 - Using the Calendar table to make "time intelligence" measures
Thanks Chandoo garu.
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?
Thanks Sir ❤🙏🙏🙏
Thank you sir
@00:27:22 - Wireframe design for our dashboard
Excellent. ❤
Profit percentage should be Divide([Total Profit], [Total Cost])
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
Thanks Chandoo! It would be great if you give the pbix file of this report.
Thank You @Chandoo
Hey Chandoo, I don't see the icons file in the description.
Great video btw!
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
Muje sikhna hai so it will be useful for my accounting field
Please make a dashboard for sourcing side. Your ideas are good.
Great video, please also provide logo in the description.
really helpful
Thank you chandoo
awesome👌👍
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!
Thank you..
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?
Sir we were waiting in telugu
Thanks bro
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.
Thanks
Thanq sir
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.
I cannot find the images and logo file
First 15m viewers😊
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?
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, not getting twitter and any mail from Your side
God Bless You 🙏🙏🙏
my vote to version 2
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
Where have you given the links for the logo?
Sir Please provide the pbix file also
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..
Can you pls share a video how to link Google sheet to Power Bi & update regularly?
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 - ua-cam.com/video/R3bZ0LLv3Fk/v-deo.html
sir i understood power bi but some DAX i am struggling to get it like big formulas
Shall I do this to showcase in my resumae
Sure. As long as you credit the source.
15:55
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
Pbix file?
Why didn’t you divide the profit by total costs to get profit percentage
Q. can we do MOM and YOY changes by quick measures function ?
You can try. I think in this case, it might work.
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.
Please give the links for the logo's
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.
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.
I am not able to find the link to download sample file
Hello sir , i cant find files used in videos like .png etc except .csv , please share those .. Thank you
🎉
Icons were not uploaded to git
We don't get the excel data 😢
can you share the data link pls
So much to learn
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.
where is the sample data?
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
HOW TO MENTION (DESCRIBE)THIS ON RESUME
How to use calculated group for label reference ?
Yeah, I don't think it is possible.
Sir data file not found