Power BI has a LOT of chart types. More than 30 of them. So, picking the correct chart for a data analysis situation is hard. Here is a picture of a frustrated Power BI developer trying to figure out the right chart for her data analysis.
In this article, let me explain when & how to use these Power BI charts along with my top tips to make them.
The Power BI Visualization (Charting) Options
Here are the 30+ charting options in Power BI (as of July 2023). In this article, I am going to cover a majority of them.
We can categorize these charts into below groups.
- Bar / Column Charts 📊
- Line & Area Charts 📈
- Pie & Donut Charts 🍩
- Card Visuals 💳
- Tables & Matrices 📰
- Scatter (XY) plots
- Maps 🗺
- Everything Else
Let’s understand when & how to use these charts now.
Bar & Column Charts in Power BI
When: Use them to compare things
Examples
Top Tips
- Use the “Legend” option to split the chart
- If you put more then one field in the “axis”, you can drilldown on the charts.
- You can also split the chart into many using the “small multiple” option. See this video to learn more.
- When using “time” or “date” axis, try the column charts instead of bar charts.
Line & Area Charts in Power BI
When: Use them to see the trends
Examples
Top Tips
- Convert line to “stepped” line chart by enabling the “stepped” option in formats.
- Enable markers to improve the line chart readability.
- When you have a line chart with date axis, you can use the “analytics” options to introduce forecast or trend lines.
- Don’t make line chart with “things” on axis. They are meaningless (most of the time).
Pie & Donut Charts
When: To understand the full picture & contribution
Examples
Top Tips
- Always customize the labels and legend to get best look for your pies (or donuts)
- Avoid making too many slices. No one likes a tiny slice of pie.
Card Visuals
When: To share single numbers, KPIs & messages
Examples
Top Tips
- Add context to your cards whenever possible. This is a powerful way to engage your audience. Read this article for more.
- Power BI recently introduced a “NEW Card” visual. Give it a try. It lets you build cards with more formatting options and it is faster than regular cards.
Table & Matrix Visuals in Power BI
When: To show details
Examples
Top Tips
- Adjust row padding on tables & matrices to make them look easy on eyes.
- Add conditional formatting to one or two fields to elevate the data.
- Apply correct sort order on your tables. If you hold SHIFT while sorting the tables, you can sort on multiple columns!
- When you have lots of data, Filter your tables down to just important points.
Power BI Maps
When: To explain geographical data
Examples
Top Tips
- Power BI has a lot of map visuals. Try them all to see which one gives you best fit for your data. My favorite is the new Azure Map Visual. Learn more about it here.
- While maps are useful, I find them clunky. So try tables or other charts from time to time.
- Do not overlay useless information like traffic on to maps. Keep them light so the focus can be on the data.
Scatter (XY) Plot in Power BI
When: To explain 2 dimensions in one picture & to explore relationships
Examples
Top Tips
- XY plots are great for exploring relationships. But remember the golden rule – Correlation is not same as causation.
- If you have a Date dimension, use it on the “Play Axis” option to make a cool animated XY graph in Power BI.
Waterfall Chart
When: To explain how things have changed from one point / place to another.
Examples
Top Tips
- Use the “explain” feature of Power BI to auto-generate useful waterfall graphs for you.
Don't forget
Don’t forget title, sub-title, legend, labels & tool-tips.
These can make or break a chart.
Examples
Top Tips
- Don’t settle for default titles. Write a great title & subtitle on your charts to elevate them.
- Highlight important data points using conditional formatting feature. Here is a tutorial.
- Set up a tooltip page to further explain your data. Here is a tutorial on Power BI Tooltips.
Everything Else...?
Use with caution.
When in doubt, try them with your audience, get their feedback and proceed as needed.
Demo Workbook with all these charts
Here is a Power BI workbook with all these visuals. Check it out to learn a bit more.
When & How to use Power BI Charts - Video
I made a short & useful video on all of the important chart types in Power BI. Check it out below for some extra tips, pointers and information on when & how to use Power BI visuals.
Watch it below or go to my channel.
More Resources on Power BI
- Course: I run an online Power BI course to make you awesome with it. Check out the program and sign up today to learn Power BI, the right way.
Here are some articles & videos to help you master Power BI:
17 Responses to “Budget vs. Actual Profit Loss Report using Pivot Tables”
Good Work, Yogesh & Chandoo! Thanks.
Hi everybody,
first sorry I am late to say something about this topic;actually I was waiting last part
second I am not accountant I am an Engineer
third """"Very Important""" the idea is not about Loss but I am sure it is profit
Based on third it shows:
1- How to use EXCEL
2- How to use pivot TABLES
3- How to collect and arrange DATA
4- How to make reports
Many Thanks
Hi Yogesh and Chandoo,
Thank you for sharing your knowledge!
You guys are great!
thanks chandoo and yogesh, thanks for you lessons, are great!....i have a idea for a budget. I try to do it..... thanks for all
Thanks a lot for sharing the most powerful tool worldwide "knowledge"
Warm greetings from Peru
Hi -
This is a really great article because it's a simple and common thing you'd want to do with a pivot table but not at all obvious how to do it! So - muchas gracias to Chandoo and Yogesh!
One thing - I couldn't get past the group error in the sample file. I would click on ungroup but it didn't seem to have any effect. I'd appreciate it if anybody has any pointers here.
-Juanito
Hi Chandoo
I am also having the group error. Can't seem to ungroup? Appreciate if you explain further on the steps required in order to get to calculated items.
Many thanks and keep up the great work.
Cheers
Adam
Hi Chandoo,
I'm struggling resolving the problem depicted below:
I have a set of data, with (among others) a "Region" field (can be APJ, EMEA, or AMS), and a "Country" field.
Unfortunately, I need to group data by the following 4 Regions: APeJ, Japan, EMEA and AMS.
I first tried to make a pivot with Region and Country in the rows (or columns), and then group Country data as per the above.
Alas, as soon as I have a new Country that appear in my data set, my groupings are broken, and I have to redo the job of ungrouping, grouping etc.
I thought I could try to use calculated item, by adding first a new column to my dataset concatenating Region_Country, and create an "APeJ" calculated item that would sum all the "APJ_*" and substract the "APJ_Japan", but again, no clue, as I can't find a way to use any wild card in those formulas.
Given that I already found extremely helpful tips and tricks in your site that helped me manage that bunch of data, I'm pretty sure you'll have a bright idea on how I can solve that one!
Thanks in advance for your lights!
Hi Catherine...
In such cases, I advice using an additional column in the data itself. You can set-up a grouping table else where with country in first column, region in second column. And then in the data, you can add an extra column and use VLOOKUP to fetch the region based on the country.
Then feed this entire data (with extra column) to pivot table and use the extra column to group the data.
Hi Chandoo,
Thank you for your prompt answer.
I finally came to the same conclusion - after a rest 🙂 . I was probably too tired Friday evening (it was rather late), having spent hours in manipulating all my surveys data so as to pull rolling averages, make nice graphs and so on, and was trying to find a complex solution when there was a simple one.
Thanks again,
Catherine
Hey,
Great post!
I for example have different database structure with the following fields :
Date, Expense, Income, Sum (Income - Expense), Category (Sales, Cost of Goods and etc).
Creating a P&L report for the whole year works great. Including gross margin % and etc.
Though, creating P&L report by QTR/Month is becoming impossible since i get the following error : “This PivotTable report field is grouped. You cannot add calculated item to grouped filed.”
Is there a solution for this kind of problem?
Like Adam and Juanito, I also cannot ungroup.
Would appreciate it if you can add a few more lines and a screenshot or two on where to put the mouse cursor to ungroup.
Hi, I have figured out the ungrouping problem. One of the earlier steps was to group by month, if you pull the month back down to the column then right click and then select ungroup, then pull the month back up so you end up with just data source and budget/actual as the headings, then you can continue on.
To solve the ungroup problem, my method is:
Copy the "data" sheet to a whole new Excel workbook
and directly work on Part 6.
And since it is a fresh copy, Excel don't show me the "can't ungroup" problem. Hope this help.
Thank you Yogesh for this wonderful tutorial.
Kent, Malaysia
Just when i thought pivots were awesome i learn about inserting the calculated fields and that makes them more awesome. chandoo where have you been all my life.
Hello - your P&L pivot version has really impressed my boss and would like to use it. I have applied it for a actual vs budget vs forecast model I have created. One problem. In your variance above the operating profit percent % variance shows 33.8% but I want it to show (0.01) point or the true diff from prior budget.
I know I can add calculation to the side but boss would like to see it in pivot table.
Please help
Thanks
I have a further query which may solve my above dilemma. Is it possible to add a column that calculates percent increase. So in the example above a new column would be added to show variance %.
Any help would be appreciated.
Thanks