Hi all,
I'm very new to the wonder of Pivot Tables and the ability to produce fantastic dashboards. I'm trying to work through a simple example, but I have hit a blocker that I cannot find the answer too.
I have a 'status' column for my data. This can only have 5 fixed values. (Almost certain, Likely, Possible, Unlikely and Rare). However, if one of those values - say rare, is not currently used and does not appear in my data, how can I show it in the pivot table? (even if the count is zero).
By the default the Pivot table will only show the first four values, as my dataset currently ONLY has those values. Is there a way to force the pivot to also show 'rare', even though no data currently has that value?
Regards,
Confused new user!
Darren.
Try changing the settings for that field to 'Show items with no data'
Ah - that option is greyed out for me, but I think you have set me off on the right path...
Sounds like I need an OLAP cube to tell the pivot what is the structure of my data-table. I need to tell it, there are 5 values. At the moment, my very basic pivot is just reading the data, and seeing only 4 values, so it assumes there are only 4.
Thank you for pushing me to the right path....
Cheers
Darren.
Hello,
As mentioned in an earlier post.
”Be sure to select a cell in the column labels area before right clicking > Field Settings (not 'Value Field Settings') > Layout & Print tab > 'Show items with no data'.”
Give it a try.
Br,
Anders
Hi Darren,
You're going in the right direction. To clarify Anders suggestion, the 'show items with no data' option is not available with Power Pivot PivotTables, and will also only work if the items are actually in the dataset at a completely unfiltered level, which it sounds like yours aren't.
You can use CUBE formulas to build your table that queries your Power Pivot model, however you need to add another table to your model first.
This table will contain all the status values (this is known as a dimension table). It can be a single column table. Then you create a relationship between your original table and the new dimension table.
In the attached file I've created a PivotTable from the two tables, then coverted it to CUBE formulas (PivotTable Analyze tab > OLAP Tools > Convert to Formulas). This will give you the formula structure. I then copied the formulas and edited them to reference the status that wasn't in the source data.
Hope that helps.
Mynda
and will also only work if the items are actually in the dataset at a completely unfiltered level, which it sounds like yours aren't.
As long as the data was in there at some point, and the pivot table options haven't been changed from the default (automatic saving of old data), then the option should still work even if the item in question is not present at all in the current data set.
But it does sound like this is a data model pivot, so the question is moot anyway. 🙂
Ooh, good idea, Velouria! Although, like you say, not relevant in this case, but crafty workaround to keep in mind for other scenarios.
Wow - Thank you everyone. This has nailed it for me. FANTASTIC.
Much appreciated everyone's help.
Regards
Darren.
