Make an Incredible Finance KPI Dashboard with Power BI in 20 minutes

Рет қаралды 58,120

Күн бұрын

🏆 Participate in my Dashboard Competition 👉 bit.ly/3xgC1WB
~
Make an incredible Power BI Dashboard to visualize your finance KPI data in just 20 minutes. In this video, learn how to take raw business data and turn it into useful insights by following these 4 steps:
1) Data Preparation - Connecting and transforming data using Power Query in Power BI
2) Data Modelling - Setting up a semantic model in Power BI with table relationships.
3) Calculations - Performing necessary number crunching using the DAX in Power Pivot
4) Dashboard Visuals & Interactions - Creating and customizing various visuals to generate our KPI dashboard.
Key topics / ideas you will learn are:
1) Power Query unpivot
2) Data Modelling with multiple fact tables
3) DAX calculations for YTD values
4) Using Emoji with DAX
5) Using FILTER function in DAX
6) Working with the "NEW" card visual and reference labels
7) Using custom data labels with graphs
8) Sparklines in table visual
9) Working with Power BI report themes
10) Smart narrative feature of Power BI
11) Table formatting for executive reporting
12) + plus many other useful tips and tricks
⏱ VIDEO CHAPTERS:
==================
0:00 - Step 1: Data Preparation for KPI Dashboard in Power BI
2:45 - Announcing Power BI Dashboard Competition 🏆
4:10 - Step 2: Data Modelling
5:26 - DAX Calculations
9:12 - Making the Dashboard
10:58 - Using Reference Label feature to add context to the KPI cards
12:01 - Win/loss chart in Power BI
13:02 - Enhancing variance Labels with Emoji
15:27 - Monthly Trend Graph
17:59 - Sales Person Performance Table
20:42 - Adding Smart Narrative with Power BI (AI generated commentary)
21:19 - Final version of the report (with bells and whistles)
📁 Download Sample Data
======================
For the competition - bit.ly/3xgC1WB
Just the dataset used in this video:
chandoo.org/wp/wp-content/upl...
📺 MORE DASHBOARDS + POWER BI
===============================
Power BI Beginner to PRO Masterclass - • Beginner to PRO Data A...
Sales Dashboard with Power BI - • How to Make a Sales Da...
HR Dashboard with Power BI - • HR Data Analytics & Aw...
Data Analysis with Power BI (live stream masterclass) - • Data Analysis with Pow...
Dashboard Design in Power BI (5 tips) - • Power BI Dashboard Mak...
SQL + Power BI Dashboard - • How to use SQL with Po...
💥 POWER BI FULL COURSE:
========================
Join my Power BI full course and learn the technology the right way.
chandoo.org/wp/power-bi-course/
#powerbi

Пікірлер: 101

@chandoo_ 2 ай бұрын

🏆Participate in my Dashboard Competition 👉 bit.ly/3xgC1WB

@sudhakarnote 2 ай бұрын

Congrats for being mentioned in the top Excel youtubers list in the world

@anniebannie221 Ай бұрын

Someone suggested me this channel and I cant thank him enough for this recommendation. words fall short.

@CairgyMmapetla 2 ай бұрын

In all my time of learning on KZfaq, i have never got so much in 20 minutes its crazy 😅 Like. Thank you!

@vijaychauhan5447 2 ай бұрын

Sir Namaste Your All videos very knowledgeable.

@chrism9037 2 ай бұрын

This was great, thanks Chandoo!

@arbazahmad7177 2 ай бұрын

Excellent.... thanks for sharing 👍

@michaelmaharlika8569 2 ай бұрын

I am very impressed, outstanding job on tis dashboard. Thank you for being you, and for your creative and intuitive insight in to telling stories with data!

@chandoo_ 2 ай бұрын

Wow, thank you!

@taofeekogunsesu2566 2 ай бұрын

You are very good. Thank you for so many information in a very short time.

@lilyfullery4779 Ай бұрын

Hi Chandoo , i feel variance should be Calculated as Total_Target - Total_Actual because target is what needs to be acheived and actual is what has been achived.

@chandoo_ Ай бұрын

In that case, you will get a positive variance when we do not meet the target. As positive numbers indicate good outcomes, the logic needs to be actual-target.

@vedhaprakash164 2 ай бұрын

Hello chandu , I am really excited to take excel course but due to price 247$ is very much high , can reduce for price for university students , hoping you could reply on these , and thanks for giving datasets for SQL sessions ,

@Vivasvan22-21zy 2 ай бұрын

Hi bro if you're interested I can provide videos only for 1k all power bi+ssas+azure

@AchuVlogs 2 ай бұрын

Excellent dashboard sir!!

@abdullahusman4539 2 ай бұрын

Thanks bro for making this video

@judeLondon 2 ай бұрын

Beautiful despite missing targets So fun. So clean. So clear

@mohemmedansari8664 2 ай бұрын

You make it easy and cool.

@Sirigineedi_Navann 2 ай бұрын

simply fantastic anna Happy Ugadi anna

@PONCEJE 2 ай бұрын

Nice smile congratulations. You looks fantastic.

@fahimreza2009 2 ай бұрын

Excellent!

@MuhammadAli-ib4xn 2 ай бұрын

Helpful thanks so much

@kuuuyajim 2 ай бұрын

You make dashboards like a walk in the park.

@godisalmighty4449 2 ай бұрын

Great. Thnks a lot sir

@mvshyamprasad09 Ай бұрын

Why I am getting difference in some values from what you got and what I got using the same method?

@shankardevkota3577 Ай бұрын

HI chandoo, in minute 8, YTD Sales Actual = CALCULATE([Total Sales Actual], DATESYTD('Calender'[Date]), this formula didnt work in my case? 'Total Sales Actual' didnt read by CALCULATE function. however it was working fine in previous formulas. Please help me with this. Thank you.

@Hercules90015 23 күн бұрын

Yes, I am also facing same issue

@zaidandhman7687 18 күн бұрын

can you please make a video to explain time intelligence functions in details

@sam02109 28 күн бұрын

Hi Chandoo, YTD Sales Actual = CALCULATE([Total Sales Actual], DATESYTD('Calendar'[Date]), this formula didn't work in my case. 'Total Sales Actual' didn't read by CALCULATE function. however, it was working fine in previous formulas. Please help me with this. YTD Variance % is also showing -100%. Thank you.

@user-bg7tw8xo7e Ай бұрын

thank you so much for the content you have been posting on KZfaq Channel. May I suggest/ask something? Can you please teach us how to create a multilanguage Dashboard in Power BI? I have been looking for something on Google, and KZfaq and so far I couldn't find anything. Please, can you help me with this?

@tjoelgeorge 2 ай бұрын

Hi Chandoo, thanks for the video, certain steps of the video have been removed, would have been better if those also were included. Anyways thanks for the video

@ibrahim82649 2 ай бұрын

Nice one Chandoo

@SyedliyakathaliAli 2 ай бұрын

Fantastic video chandoo❤🎉

@chandoo_ 2 ай бұрын

Thanks 🤗

@pgkannan 2 ай бұрын

Happy Ugadi

@theMilgarStudio 2 ай бұрын

Hi Chandoo, when am creating the visual for measure YTD actual, am getting card visual as " Blank ". Could you please help me with this? Hope the measure is right! : YTD sales actual = CALCULATE([Total sales actual], DATESYTD('Calendar'[Date]))

@michelcockerham2109 2 ай бұрын

me too!

@talknow2859 Ай бұрын

Me too!

@nganpham8431 26 күн бұрын

me too! can anyone help me to resolve??

@na-zw8ju 26 күн бұрын

This youtuber cut a lot of important moments. If you get stuck for this, in "Actual", change the data type of the "Date" column from Text to Date.

@nganpham8431 19 күн бұрын

@@na-zw8ju Thank a lots. can resolve it now

@Malarvizhi.S-kc5dq Ай бұрын

Hi Chandooo Anna, I have reached till dax function - target for the month, but I am not getting any expected value, it results this _ _ and there are no errors in the formula. Can you please help me rectify this?

@jayaprakashh2604 2 ай бұрын

Hi Sir, I it’s helpful I learned lot from your video I would like to request your all data shows only about sales data if you can do some dashboard with supply chain and inventory management data it will be helpful for other professionals. It’s just request. Thank you 🙏🏻

@chandoo_ 2 ай бұрын

Noted

@vaishaliniramakrishnan6241 2 ай бұрын

What is the DAx to be used to get previous month

@dhaval1489 2 ай бұрын

Chando Bhai, can you please do a time series analysis in Power BI it seems quite complicated

@Rashid-qz7pt Ай бұрын

Hi Chandoo! When you choose an individual sales person at 20:40 you get Month Target Reached = 7, however the little chart below clearly shows smth different, besides above it says 'we met targets 2 out of 14 month. How could we fix that?

@swetaleenasatpathy6125 Ай бұрын

Hi, you can fix this issue by changing the bar/column charts to line charts. Line charts are perfectly filtering out data for individual salesperson. I am yet to figure out the reason.

@kavitadaga6689 2 ай бұрын

I love how this video is challenging a beginner like me to figure out nuances instead of spoon feeding every step. I managed to solve most of my problems, but somehow months target reached is still 0 and my column chart for target vs actual sales shows no data for any of the in between months. Why?

@kavitadaga6689 2 ай бұрын

Ok, got a part of the answer - it is treating the dates as in mm/dd/yyyy format!

@thunder6179 2 ай бұрын

Sir please do a videos kn informatica

@guedidzineb8714 2 ай бұрын

Hello, I hope you are well. I am a student. I need help with the decision support system. I am looking for training in this subject. Is it a requirement to understand the ssis ssas ssrs program or Power Bi just to implement my graduation project?

@user-tg9uz1pd3c 2 ай бұрын

make vdo including copilot in BI

@GautamJoshi-nx3nh 5 күн бұрын

why in my hand YTD returns blank

@vijaychauhan5447 2 ай бұрын

Sir Can you Make market stock portfolio report in Excel by Google finance.

@vishwanathm4207 Ай бұрын

Hi @Chandoo Sir, I'm a Power Bi developer with 4+ yrs exp My Skills SQL, Power BI & Knowledge on Tableau Please suggest me additional course which will enhance my career Growth. Currently I'm in middle of learning Power of APPS & Automate, Shall I learn this ADF & Python Stuff or Power Platform?

@zaidandhman7687 18 күн бұрын

when I changed the date data type after unpivoting the columns, it gives me wrong dates ( all the dates year were 2024) , this affect the time intelligence function hence there is no year but the year 2024... how I can fix that….. please advise

@Amathylar 2 ай бұрын

Do you make custom dashboards? I want one for my business!

@indrajaravi6945 2 ай бұрын

I know someone who makes custom Power BI Dashboards for businesses. He has helped businesses 2x their revenue! Let me know if you'd be interested.

@emmanueloladipojohnson890 2 ай бұрын

Do you have a beginner video on Power BI and DAX formula?

@chandoo_ 2 ай бұрын

I do. Check this - kzfaq.info/get/bejne/hrdln6R9yaysc4k.html

@pogulasai3930 2 ай бұрын

Hai chandoo Anna, when I have unpivoted other columns and shriked the remaining columns to date and sales and when I tried to load the data I am getting an error: "Column 'Sales Person' in Table 'Actual' contains a duplicate value 'Andria Kimpton' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table." Please Help

@chandoo_ 2 ай бұрын

The tables Actual and Targets have no direct relationship. The relationships are between Actual - calendar Actual - sales person Target - Calendar Target - sales person

@shaguftanighat-de3sj 2 ай бұрын

Hello, Chandoo Brother, you skip two formulas 1. YTD Variance 2. YTD Variance % during editing the video so I Can't understand it. Please tell me how can I calculate these formulas? Because I am beginner.

@rajishn 2 ай бұрын

my win loss chart is all showing 1 and all in green.. what could be the reason

@harshithharsha4066 20 күн бұрын

tables are not connecting

@mdshahnwaz5853 2 ай бұрын

Question- Why the dashboard showing wrong while filtering individual team like Tempo team showing 4 months out of 14 months however if see count the green variance column that only 3 on top of it.

@chandoo_ 2 ай бұрын

In my case, it shows 4 green bars and says 4 out of 14 months. So may be you have a horizontal scrollbar on the graph?

@mdshahnwaz5853 2 ай бұрын

@@chandoo_ Thank you for replying, no I'm taking about in your dashboard please count the column which is labels as green percentage% on top like team Temp I can see there only 3 column which mentioned green percentage for variance surpass vs target sales, also I know their in-card visuals 4 months showing which is fine, please suggest if understanding something incorrect.

@chandoo_ 2 ай бұрын

Not sure what you mean. If it is shown in the video, can you share the timestamp so I can examine further.

@thes6z810 2 ай бұрын

Ya sure here is the time which is Team "Tempo" filter 17:48 see the top where header title is we met target 4 out of 14 however count the month column which lebels as green🟢 Dot % that is only 3 why that should be 4 because that's 🟢lebel showing we reached the target sales value

@chandoo_ 2 ай бұрын

That is because of label density. Power BI automatically hides labels when they are too many of them. It just shows labels for important points. You can adjust the density to 100% (thus showing all labels all the time). By default it auto-hides labels for some points.

@sowmyav5878 2 ай бұрын

Hi sir, how to create YTD variance. Can u plz explain

@chandoo_ 2 ай бұрын

Assuming you have already calculated YTD Actuals and YTD Targets, YTD Variance = [YTD Targets] - [YTD Actuals] To calculate YTD Actuals, use this logic: =CALCULATE([Total Actuals], DATESYTD(Calendar[date])) Same pattern for the YTD Targets too.

@nitinsharma2019 2 ай бұрын

@@chandoo_The video should be in a simple form so that beginners can learn from it as well

@chandoo_ 2 ай бұрын

Come on mate. You can't expect a beginner to straight away make a dashboard. Take baby steps with other videos. I have plenty that go slow and explain all the basics. If in every video, I open by saying now start your computer and download Power BI then no one would watch.

@Saurabh-gh7ym 2 ай бұрын

hey chandoo in the video the actual has only three columns but in reality there are 14 columns how am i supposed to make relationships same as that on the video?? please help u skipped that part

@chandoo_ 2 ай бұрын

Hmm.. I explain that step in the data prep part. You need to unpivot both actual and target tables.

@a-31saurabhghore46 2 ай бұрын

Got it thanks man loved the tutorial

@nickcanque9106 2 ай бұрын

I'm having problem with my output for YTD Sales Actual, YTD Sales Target, YTD Variance, YTD Variance % showing - - YTD Sales Actual = CALCULATE([Total Sales Actual],DATESYTD('Calendar'[Date]))

@tjoelgeorge 2 ай бұрын

Hello Nick, please use the following YTD Sales Actual = CALCULATE([Total Sales Actual], DATESYTD(Actual[Month])) YTD Sales Target = CALCULATE([Total Sales Target],DATESYTD(Targets[Month])) YTD Variance = [YTD Sales Actual]-[YTD Sales Target] YTD Variance % = DIVIDE([YTD Variance],[YTD Sales Target])

@nickcanque9106 2 ай бұрын

@@tjoelgeorge tnx

@michelcockerham2109 2 ай бұрын

@@tjoelgeorge When I do this, I get an error message. Any ideas why?

@talknow2859 Ай бұрын

i did the same and was prompted to format the the date tables to date type. Then youll get the right answer.@@michelcockerham2109

@BarshaD062 14 күн бұрын

Hi could anyone help me, after adding YTD Sales Actual data into Reference label Series for all the 5 labels I'm getting same value as YTD Sales Actual $2M

@chandoo_ 14 күн бұрын

You need to set the label individually to each item.

@BarshaD062 12 күн бұрын

@@chandoo_ Hi, Chandoo. Yes, I have done that. In all five categories, I'm getting the same figure as YTD Sales Actual $2M.

@chandoo_ 9 күн бұрын

Check the measure logic. Test by first making a card with all 5 values.

@mohdimroz2769 13 күн бұрын

sir images not work 😢

@swetaleenasatpathy6125 Ай бұрын

Hi Chandoo, please do reply to my query. Why is the whole dashboard behaving weirdly when filtered with salesperson name?

@chandoo_ Ай бұрын

Hmm.. nothing unusual there. When you select a salesperson (in the table), all visuals are showing the values for that salesperson. Are you seeing any wrong values?

@swetaleenasatpathy6125 Ай бұрын

@@chandoo_ somebody else (Rashid-7z..) has also commented regarding the same issue. Column chart title is not getting updated dynamically, columns are looking weird and the little chart below all the KPI cards is also not showing the correct values when you select a single salesperson from the table. I replaced both of these charts as Line charts, and they are now showing the correct values for a salesperson. I am unable to figure out the reason behind discrepancies in column charts.

@swetaleenasatpathy6125 Ай бұрын

@@chandoo_ When we filter the dashboard by selecting a single salesperson from the table, the little chart below the KPI cards is showing some really weird bars, also the column chart gets distorted completely. The chart heading does not get dynamically updated as well. I replaced all the column charts with line charts and then everything is working as expected. I am unable to figure out the reason behind this. There is another comment by @Rashid-qz7pt highlighting the same issue. You can also check your video from 20:36 mins. I can see the same issue.

@chandoo_ Ай бұрын

I think this is a highlighting "bug" in Power BI when CF rules are also used. That is why our tiny win/loss chart is showing weird colors (the columns are right) when clicking on a person. Reg. chart title not chaging, that is expected. As we have the interaction set to "highlight" rather than "filter", the title won't change. When you use a line chart, it always "filters" so, the title changes. Everything else is as per normal behavior, but might seem strange if you are not used to seeing such interactions.

@dikkhatrainingconsultancy8526 2 ай бұрын

If I don't use Var and write directly this formula COUNTROWS(FILTER(all('Calendar'),[Variance]>0)), It is still working. Why Var is required in this case

@chandoo_ 2 ай бұрын

You can do that too. I like using variables so that my DAX looks better and lets me add conditions easily later.

@dikkhatrainingconsultancy8526 2 ай бұрын

@@chandoo_ thanks for your reply