Pivot table - hide a column which reflects twice

[Navi]

Hi Team,

Need a help with pivot table.

I have a pivot with country in row labels, active and inactive headcount as two columns. now i need to bring in active headcount % (which is active Headcount/ row total).
Currently when i drop down the headcount in values twice, i get as active headcount, active headcount%, inactive headcount and inactive headcount %.. now i need to hide this inactive headcount% alone. how should i do the same. I will not be able to hide the entire column since i will have few more pivots below this data.

thanks in advance.

Regards,
Navi

 

[GraH - Guido]

Use the data model (PowerPivot) for the most solid option. Make your (DAX) measures, then slide in only needed measures in the value grid. No need for hiding anything.
You would have something like:
- HeadCount: = DISTINCTCOUNT(Table[EmployeeID])
- All Head Count: = CALCULATE([HeadCount];ALLEXCEPT(Table1;Table1[Country]))
- Head Count Active: =CALCULATE([HeadCount];filter(Table1;[Active]=true);ALLSELECTED(Table1[Country]))
- Pct Active HC: = DIVIDE([Head Count Active];[All Head Count])

Pivot with details:


"Collapsed"


Measures show up as "Fx", not all of them are needed in the pivot value section: in my simple example HeadCount is not used inside the pivot, but derived calculations are.

 

[Navi]

Thanks a lot