Dimension(Column) missing in Pivot when there is Null

[#1]

Hi All,

I have created a Pivot table using below data.

ID	Name	EMP Name	Department	City	State	Amount
1	Suman	Suman	A	Hyd	AP	1000
2	Krishna	Hari	B	Bang	Ktk	2000
3	Ram	Gopal	C	Chennai	TN	3000
4	Rakesh	Puri	D	Pune	MH	4000
5	Giri	Giri	E	Delhi	Del	5000

I want to show data for "Department", "City" and "State" when both "Name" & "EMP Name" matches. It's working fine when i look at complete data.

But i am facing issue when i selected single record(where "Name" and "EMP Name" is different.

I want to see all the dimensions in the pivot table even it has null(Due to my condition).

Can anyone help me on this.

Thanks in Advance!!

[krishnacbe]

In pivot table you wrote the conditional expression to hide the values. Instead of null values try with N/A or some other text values.

Pivot table nature is there is no value in Department column, so it hides city and State.

[CarlosAMonroy]

Hi Krishna

You can try adding values:

=IF(Name=[EMP Name], Department,'')

Thanks,

Carlos

[sunny_talwar · Accepted solution]

May be this

=if(Name=[EMP Name], State, Dual('-', 1))

[#5]

Above Like Sunny reply

PFA

Try like this

if(Name=[EMP Name],sum(Amount))

and uncheck the   presenattiontab>supress zero values option