All articles with 'power pivot' Tag
As part of our Excel Interview Questions series, today let’s look at another interesting challenge. How can you analyze more than 1 million rows data in Excel?
You may know that Excel has a physical limit of 1 million rows (well, its 1,048,576 rows). But that doesn’t mean you can’t analyze more than a million rows in Excel.
The trick is to use Data Model.Continue »
Work in HR and use Power BI? You are going to love this extensive, powerful and useful Employee Turnover Dashboard. In this detailed article, learn how to create your own attrition dashboard system with Power BI. Full example workbook, video and sample data included.Continue »
In this installment of Power Mondays, let’s go the very basics of it all.
What is Power BI?
Power BI is a software to create and publish interactive, web-enabled reports & visualizations for your audience. You can use Power BI on a PC or web to create things. Once you publish a report (or few visuals), your audience can consume them by using any device – PCs, Macs, Web browsers, Apps on cell phone / tablets etc.Continue »
Welcome to Power Mondays. Every Monday, learn all about Power BI, Power Query & Power Pivot in full length examples, videos or tips. In this installment, learn how to get top 5 list with a twist.
Let’s say you are analyzing sales data and you want to know who are your top 5 sales persons?
Of course, this is simple, you just create a pivot to see total sales by person and then sort the pivot. First five rows have the answer you need. You can even apply a value filter > top 5 to show only their data.Continue »
Welcome to Power Mondays. Every Monday, learn all about Power BI, Power Query & Power Pivot in full length examples, videos or tips. In the first installment, let’s take a look at something we all can related to – Money.
We all know that Power BI is good for creating awesome visual experiences. Today let me share another fun way to use Power BI – to build a calculator. Learn how to create nest egg calculator in this Power BI parameter example tutorial.Continue »
Ever wondered how to get percentage of another value in Excel pivot tables, like this: In this tip, learn how to create such calculations using Excel pivot tables. Note: this tip is not compatible with older versions of Excel. If you are using Excel 2007 / 2010 / 2013, then please install free Power Pivot […]Continue »
Commonwealth games 2018 have ended in the weekend. Let’s take a look at the games data thru Power BI to understand how various countries performed.
Here is my viz online or you can see a snapshot above.
Looks good, isn’t it? Well, read on to know how it is put together.Continue »
Before we begin:
Today is the last day for enrolling in our Power BI Play Date. Don’t miss out on this amazing opportunity to learn, use and benefit from Power BI at your work. Check out my online class and sign up before the doors close at midnight. Click here.
Let’s get our Simpsons on then.
D’oh, How often Homer says his favorite things?
Here is the visualization to explore Homer’s (and other character’s) favorite sayings in 27 years worth of Simpsons episode. Click on the image to play.Continue »
Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?
Finding the first responded team is tricky.
Today let’s take up this problem as a case study and understand various methods to solve it. We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.Continue »
Mary Ellen, one of our readers, has an interesting conundrum,
I have some data that goes to Pivot table then to pivot chart. There is a slicer to filter the data. But when I slice, my pivot chart gets messed up. How to have the slicer, but still see the insights in the chart?!?
See above demo to understand:
This is because when you slice by a school, the pivot table gets filtered and hence % row total for that school becomes 100% (as there are no other schools).
How to fix the problem? The easy answer is to remove the slicers. But we want to have our slicers and eat a slice of them too. So we crank up the Excel awesomeness valve and get to work. There are two ways to achieve what we want.
- Old school method: Two pivot tables, some formulas & a line chart
- New Excel method: Power Pivot and a line chart
Read the rest of this article to know more.Continue »
Hello all, prepare to be amazed! Here are 43 creative, fun & informative ways to visualize KPI data.
About a month ago, I asked you to visualize KPI data. We received 65 entries for this contest. After carefully reviewing the entries, our panel of judges have discarded 22 of them due to poor charting choices, errors or just plain data dumps. We are left with 43 amazing entries, each creatively analyzed the data and presented results in a powerful way.
How to read this post?
This is a fairly large post. If you are reading this in email or news-reader, it may not look properly. Click here to read it on chandoo.org.
- Each entry is shown in a box with the contestant’s name on top. Entries are shown in alphabetical order of contestant’s name.
- You can see a snapshot of the entry and more thumbnails below.
- The thumb-nails are click-able, so that you can enlarge and see the details.
- You can download the contest entry workbook, see & play with the files.
- You can read my comments at the bottom.
- At the bottom of this post, you can find a list of key charting & dashboard design techniques. Go thru them to learn how to create similar reports at work.
Thank you very much for all the participants in this contest. I have thoroughly enjoyed exploring your work & learned a lot from them. I am sure you had fun creating these too.
So go ahead and enjoy the entries.Continue »
Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.
Slicers – what are they?
Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.
For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.
- Add region as report filter and filter for the region you want.
- Add a slicer on region and click on the region you want.
With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.
Read more to learn all about slicersContinue »
We all know pivot table functionality is a powerful & useful feature. But it comes with some quirks. For example, we cant insert a blank row or column inside pivot tables.
So today let me share a few ideas on how you can insert a blank column.
But first let’s try inserting a column
Imagine you are looking at a pivot table like above.
And you want to insert a column or row. Go ahead and try it.Continue »
If you listen carefully, you can hear my screams of joy. I have 2 very happy news to share with you.
- I am speaking at Excelapalooza Excel conference, happening at Dallas (Texas) between September 21 to 24.
- I will be conducting an Advanced Excel, Dashboards & Power Pivot masterclass at Houston (Texas) between September 15 to 18.