Trick to show items with no data in PivotTables - including Power Pivot!

Show Items with no Data in PivotTables is a handy setting that allows you to show items in the PivotTable even if there isn’t any data for them.

0:18 Example Data
0:39 Regular PivotTable
1:24 Show Items with No Data setting for regular PivotTables
1:52 Power Pivot PivotTables
2:38 Item Dimension Table
2:57 Create Relationship
3:34 Write Measure
4:38 Example 2: dates

[vijaykumarc.a.]

I searched for 30-60 minutes before arriving at this video which solved my problem of 'show fields with no data' Thank you...solution: use filed settings instead of pivot options. there in layout and display select 'show..."...earned one subscriber

[ernestochavez]

Even when your solutions are excellent, there are always new additional pieces of information in your videos that make them even more valuable. Thank you so much.

[psykco]

You are my savior... I was going round and round on this with no solutions... THANK

[kevingodsave]

I think you have a bug in my office and know the problems I'm trying to solve! Your teaching style and content are the best on YT. Thank you!

[fabiruchis]

Thank you so much! I'm currently doing my thesis for my master degree and I've been using pivot tables a lot. Your videos have been of great help!

[Rjtucker]

Thanks Mynda! This example illustrates the benefit of good data modeling practices with well constructed dimension and fact tables, proper relationships and effective use of measures. Very nicely done, with great explanation!

[peicai]

You’ve never failed to amaze me with such clear cut and simplest explanation! Awesome!

[horseshoecrabs]

This was SO hard to find, thank you very much for this video

[flyingHubby]

I had this problem yesterday. Magic that your video comes today. Thanks ❤️

[wayneedmondson]

Hi Mynda. Great lesson! A little DAX solves the problem. I noticed that the data model Pivot Table in the video was sorted alphabetically by Region. On a standard Pivot Table, sort order can be handled by a Custom List. I think on a Data Model table that is not the case (i.e. can't use Custom List sort). I wanted the order as N, S, E, W and solved the problem by loading the two tables to Power Query and added a Conditional Column to the Region table with the following M code: = Table.AddColumn(Source, "Sort Order", each if [Region] = "North" then 1 else if [Region] = "South" then 2 else if [Region] = "East" then 3 else 4). This gave me a column with 1, 2, 3 and 4 mated to rows with North, South, East and West in the Region table. I then closed and loaded both to connection only and added to the data model. Then, opened Power Pivot and set up the relationship on Region and created the Total Count measure. I then defined the sort order of the Region column in the Region table by my Sort Order column. Once done, when I created the Pivot Table, the order of Region was North, South, East and West, as desired and could be revised by going back to the conditional column step and changing the conditions. If I wanted that kind of control from the worksheet, I could add the Sort By column to the original table in which case all it would take is a Refresh to achieve a new order. This was a fun feature to add and a good learning experience. Thanks sharing your knowledge and tips :)) Thumbs up!!

[mohamedrazaa]

Hi
Your contributions towards the community is awesome,
If Microsoft sees this, definitely they will add this missing feature in the near future.

[douyeewilly]

Thank you!!! Your answer is better than the personal in Microsoft

[aleksandar]

great teaching method. you make it super easy to follow and understand. thx alot.

[alparkson]

Many many many many thanks! This is exactly what I needed

[felipe]

As always, Mynda offering a simple, quick and amazing technic to solve common issues!!!

Thanks Mynda!!!

I was wondering how can you add in the tab Insert, the different options to build the pivot table, I mean from the option Pivot Table in the ribbon Insert and get the data for the table from different resources min=1:54

[pavel_stafeev]

Thank you! It really helped. Have a nice day.

[rajanghadi]

I was not knowing that show zero items is not available in Power pivot. Thanks for adding to my knowledge.

[vivienmarius]

you are the best. Thank you for this trick

[damorgman]

Life saver!! Is this still the best way to do this 2 years later? any new functions or functionality to accomplish this?

[austindoebler]

Thanks so much Mynda! I do have a follow-up question... Is it possible in Power Pivot to only show items with no data for certain fields? I would like the main rows to always show but when drilling down would like only rows with data to show (i.e. values not equal to 0).

Thanks in advance!