Displaying row labels where there is no value

[masplin]

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]

Hello !

Are you looking for this:

Excel Workbook
	A	B
2	XXX	Not Blank
3		B3
4		B4
5	5	Not Blank
6	XXX	Not Blank
Sheet1

 

[masplin]

What I'm looking for is as follows. When I set up the pivot with no slicers activated it looks like this
[TABLE="width: 183"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LS1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]LS10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


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?

[TABLE="width: 183"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LS11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]LS20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]LS22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Gyms[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Count of Company[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Total Contracts[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]



-- removed inline image ---


Hope that's clearer

Mike

 

[MD610]

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]

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])