# Displaying row labels where there is no value



## masplin (Oct 5, 2012)

Thought the options box was for doing this but doesn't seem to work.

I have a list of 79 gyms in a city of which I have contracted with 14. I have sorted the gyms by post code and there are about 25 postcodes.  I have put all the gym names in a vertical slicer and selecting all the ones contracted and potential new signings. I'm looking to get a certain % in each postcode to give me uniform coverage.  I'm trying to build a pivot table that for each post code gives me the total gyms in the post code (using the allexcept function)  and the total from the clicks in the slicer.  This all works fine except the pivot table only shows the postcodes for the gyms I have selected.  I wanted the pivot to show every post code that has a gym with a zero for the number of gyms if I haven't selected one in that postcode.

In the options I can click on "show items with no data on rows" and columns. The "display item label fields when no fields are in the value area" is ticked but greyed out.  Are any of these meant to show what I'm looking for?  I was expecting what I had to work because I have a measure 

=CALCULATE(counta(leeds2[Company]),ALLEXCEPT(leeds2,leeds2[Postcode District]))


This counts the companies and ignores the selection of companies in the slicer. It does this fine but only shows for postcodes driven by the slicer selection.

any help appreciated as sure it is a simple answer!

Mike


----------



## Michaels (Oct 6, 2012)

Hello ! 

Are you looking for this:

Excel WorkbookAB2XXXNot Blank3B34B455Not Blank6XXXNot BlankSheet1


----------



## masplin (Oct 7, 2012)

What I'm looking for is as follows. When I set up the pivot with no slicers activated it looks like this 

Row LabelsLS1Total Gyms8Count of Company8Total Contracts2LS10Total Gyms1Count of Company1Total Contracts0LS11Total Gyms1Count of Company1Total Contracts0LS12Total Gyms2Count of Company2Total Contracts0LS13Total Gyms2Count of Company2Total Contracts0LS15Total Gyms3Count of Company3Total Contracts0LS16Total Gyms8Count of Company8Total Contracts1

<tbody>

</tbody>

If I select something from the slicers the table shrinks because the Count of companies has no value for the other postcodes so LS1 has disappeared. I would like LS1 to still show with total gyms =  8, count of companies =0 or blank and  total contracts = 2.  As I said i  thought because the 2 total measures has "allexcept(postcode)" they  would appear like this.  I'm not sure what the various option settings  would make any difference.  Is there someway I can create a measure for  "count of companies " that forces a 0 instead of no result?


Row LabelsLS11Total Gyms1Count of Company1Total Contracts0LS12Total Gyms2Count of Company1Total Contracts0LS13Total Gyms2Count of Company1Total Contracts0LS15Total Gyms3Count of Company1Total Contracts0LS16Total Gyms8Count of Company1Total Contracts1LS20Total Gyms3Count of Company1Total Contracts0LS22Total Gyms2Count of Company1Total Contracts0

<tbody>

</tbody>


-- removed inline image ---


Hope that's clearer

Mike


----------



## MD610 (Oct 8, 2012)

I think this could be easily solved by creating a measure that does have a value for every row.  Depending on your data, some type of count() on your row label would probably work.  Then make this the last measure you add to you Values area in the Pivot.  Now, you can just hide the column with that measure in excel and you won't see it, but every row will remain no matter how you slice because there is always a count.


----------



## MD610 (Oct 8, 2012)

masplin said:


> Is there someway I can create a measure for "count of companies " that forces a 0 instead of no result?



Sorry, didn't see this question.  Have you tried taking your current count measure and wrapping it in an IF() like:

=If(isblank([Count of Companies]), 0, [Count of Companies])


----------

