• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table - hide a column which reflects twice

Navi

Member
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
 
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:
73371

"Collapsed"
73372

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.

73373
 
Back
Top