Trick to show items with no data in PivotTables - including Power Pivot! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Wayne
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!!
reply
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!!
reply
Juliana
HI, I have a question. If I have a pivot table with a filter (for example NY city), and I change the data source. Now in the new data i do not have NY City, so, the pivote table show other datas different to the filter because the filter chousen doesn't exist now in the data set. So how can I do if I want that the pivot table do not show nothing if the filter doesn't exist in the data set?. Thank you so much, I appreciate your help in this.
reply
HI, I have a question. If I have a pivot table with a filter (for example NY city), and I change the data source. Now in the new data i do not have NY City, so, the pivote table show other datas different to the filter because the filter chousen doesn't exist now in the data set. So how can I do if I want that the pivot table do not show nothing if the filter doesn't exist in the data set?. Thank you so much, I appreciate your help in this.
reply
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
reply
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
reply
IsMirDochLattens
Thank you so much for your free learning videos! They are so much more valuable than any Microsoft article I could find.
It's kind of difficult to get into PowerPivot and DAX all on your own. It's the -real life- examples from start to finish that help me a lot.
reply
Thank you so much for your free learning videos! They are so much more valuable than any Microsoft article I could find.
It's kind of difficult to get into PowerPivot and DAX all on your own. It's the -real life- examples from start to finish that help me a lot.
reply
Hira
Hi I am stuck and really need your help
I want to add incentive calculation in a cell if contains particular text from another cell in same sheet
How can I do that also if I have 3 different company name having different level of incentives
Awaiting for your response
reply
Hi I am stuck and really need your help
I want to add incentive calculation in a cell if contains particular text from another cell in same sheet
How can I do that also if I have 3 different company name having different level of incentives
Awaiting for your response
reply
Rjtucker60
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!
reply
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!
reply
mans0011
Thank you for this video Mynda! Perfect solutions to problems I-ve got. Any ideas for formatting time axes and vertical grid lines to easily show where each month begins?
reply
Thank you for this video Mynda! Perfect solutions to problems I-ve got. Any ideas for formatting time axes and vertical grid lines to easily show where each month begins?
reply
Ernesto
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.
reply
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.
reply
Mohamed
Hi
Your contributions towards the community is awesome,
If Microsoft sees this, definitely they will add this missing feature in the near future.
reply
Hi
Your contributions towards the community is awesome,
If Microsoft sees this, definitely they will add this missing feature in the near future.
reply
Carlos
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!
reply
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!
reply
Add a review, comment
Other channel videos
