In the third session of Chandoo.org podcast, we are going to get BI curious. 😉
Not that kind you silly, We are talking about Business Intelligence, Big Data, Power Pivot & other Power BI family members.
First some good news: We hit #1 on iTunes in our category
That is right. Around 14th of March, we hit #1 in our category of podcasts. Yay!!!
Please take a minute to subscribe to our podcast:
- Chandoo.org Podcast is on iTunes: Click here to subscribe.
- Our show is on Stitcher and Windows Phone Podcasts too: Click here to subscribe on Stitcher. For Windows phone, search your podcasts app for our show.
- As always, you can get latest episodes, show notes & resources from our Podcast page.
What is in this session?
In this session, I am happy to feature Mike Alexander – Microsoft MVP, Author, Blogger & a good friend. Mike talks about how Excel is shaping the BI (Business Intelligence) revolution with advent of Power BI functionality.
- 10% discount on Mike & Bill’s upcoming Power BI Bootcamp in USA
- Free Excel Dashboard Tools add-in
Listen to the podcast to know how to get these.
You will learn,
- Introduction, what Mike is up to these days?
- What is BI, what does it mean to an average Excel analyst?
- What BI capabilities Excel has – brief intro to each of them
- Power Pivot & what it does
- Power Query & why it is important
- Power View & how it works (and where it sucks)
- Power Maps
- How to learn about these new technologies
- Recommended Books
- Websites
- Courses
- Live classes
- Special gift for our listeners
Go ahead and listen to the show.
Podcast: Play in new window | Download
Subscribe: RSS
Links & Resources mentioned in this session:
Mike’s website – Data Pig Technologies
Books mentioned
- Microsoft BI Tools for Excel Analysts by Mike, Jared & Bernard
- 101 Ready to use Excel Macros by Mike Alexander
- Excel Dashboards & Reports by Mike Alexander
- Power Pivot for the Data Analyst by Bill Jelen
- DAX Formulas for Power Pivot by Rob Collie
- Power Pivot Alchemy by Rob Collie & Bill Jelen
- Author profiles of Chris Webb & Mike Alexander
Power Pivot related articles & resources
- Why Excel shines & where it falls compared to other BI tools [PowerPivotPro.com]
- Introduction to Power Pivot & how to use it?
Add-ins mentioned:
- SQL Server Data Mining add-in
- I could not get a link to the segmentation, clustering add-in I used way back in 2006.
Dashboards mentioned:
Courses mentioned:
Mike & Bill’s upcoming Live Class
Transcript of this session:
Click here to download the transcript.
Thanks to Mike
Thanks Mike for joining me and sharing your knowledge, humor and passion with our podcast community. As always, it was fun talking to you. I look forward to featuring you again in our show sometime.
- 10% discount on Mike & Bill’s upcoming Power BI Bootcamp in USA
- Free Excel Dashboard Tools add-in
Listen to the podcast to know how to get these.
If you enjoy the podcast…,
Please do these 2 things. It means a lot me & our community.
- Subscribe to our podcast on iTunes
- Please leave an honest review on iTunes by clicking here.. It will help the show & its rankings immensely. Thank you so much.
Do you use Excel’s BI capabilities?
Do you use Power Pivot et al? How do you like them? Do you feel these tools empower you & your business? Please share your experiences & thoughts in comments.
21 Responses to “Distinct count in Excel pivot tables”
The distinct count option works well but I have found that if I have a date field and want to group by year, month, etc. that option seems to be disabled. I need to do both, distinct count and group by year/month.
Example data; sales orders with item quantities with dates.
Challenge; sum the item quantities, count the distinct orders and group by month. How do I do this?
Perhaps that's not possible due to the grouping?
@Al... When you use data model based pivots, you cannot group values manually anymore. Why not use Excel 2016's default date grouping option? In this case we have just a few dates, so Excel is not grouping them, but if you have an year's worth of data, when you make the pivot with date in the row label area, Excel automatically groups them. If you have fewer dates or want to use your own grouping, just create a table with all dates, add columns with month, week, year etc. Then connect this table (these types of tables are usually called as calendar tables) to your data on date field as a relationship. Now you can create reports by month, quarter etc easily.
Is this the only way to do it in 2013? I find it rather cumbersome to have to create another data table listing dates with the another column for MONTH() and YEAR() to be able to summarise data for senior level...
I know people find adding calendar tables cumbersome, but it is a best practice and let's you add more layers of analysis quite easily. For example, adding analysis by weekday vs. weekend or by financial quarter or YTD calculations (you would need either Power Pivot DAX or some very carefully setup pivot table value field settings)
I had absolutely no idea this was possible. Very useful, nice work!
Doesn't work for 2010 version though (or at least not my works version)
Hi ,
The post has the following in it :
These instructions work only in Excel 2016, Office 365 and Excel 2013.
when i have 2 different Pivot tables, one without the enabled “Add this data to data model” option, and the other one with it enabled.. is there anyway i can link slicers between them?
if the answer is NO,, what to do ?
Quick note, the “Add this data to data model” option is not available for the Mac version.
perhaps outside scope of this article but I have found when I attempt to create a pivot table from an external data source (connection to a sql view) the "Add this data to data model" becomes greyed out. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?
Is there a way to still add a calculated field when using distinct count?
I found I can't change the date source after tick the " add this data to the data model", can you help to adv how to change the date source in such case?
Is there a way to update the source once you have added to the data model? I receive a new spreadsheet weekly and would like to update the connection so my tables pull from the new source.
Hi Crhis, I like how you have hulk (superhero) as your avatar. Do you know that there is a superhero in Excel too? It's Power Query. You can use it to solve your problem in a simple click. Here an intro if you need some guidance.
Powerful Introduction to Power Query
A big Thank you. It worked.
Hi, have survey data that I need to analyze but the challenge is that my key fields are showing horizontally. I tried to transpose the fields using Power Query, but unfortunately the new fields are returning same values on a pivot table despite using distinct values
How I can a do a pivot table with discount conts in some columns and then generate shor report filter pages. pls it drives crazy
Hi. Why grand total pivot of distinct count is 13? shouldn't it be 67?
Great Answer! Saved me lots of time!
Thank you!!!
Worked awesome! Thanks!!
Hi Chandoo,
I am using pivot tables for distinct count and now I need to update them with new set of data. But when I update the source data, all the columns and formatting of Pivot table disappears and I need to build it from Scratch.
Is there a possibility that I can update the source data with new rows added and also retain my pivot tables?