Show Value / Grand Total after filter (stop 100%)

[spanksy]

I have a simple file of data for Employee, Country and Retention (values = <2 years, 2-5, 5-10 or 10+). Just 3 columns of data.

I need to show the % of each Retention group by Country in a Pivot. To do so I've set Retention as a % of column (Country) total.

However, as you'd expect when I add a slicer or filter on the Retention, the %Value becomes 100% because the Value and the Grand Total values become equal.

What I need to do is maintain the original Grand Total for the Country, and show the value of the Retention % based on Count([Retention])/Count([Staff Member]) by country.

e.g if Australia has 14 staff with Retention as '< 2 years' out of 29 total Australian staff, I need the '< 2 years' value to display as 48%

In SQL I would nest the distinct values against a summary table of values. However in Excel and Power Pivot I can't figure out how to do this.

Any help appreciated

 

[spanksy]

Self resolved as follows:
1) Used Get & Transform to create 2 data sets: 1) RentitionCount and TotalEmployees
2) RetentionCount = Transformed and GroupBy Country then Retention to Count Rows of Employees e.g. Australia: <2years = 14
3) TotalEmployees = Transformed and GroupBy County to CountRows of Employees e.g. Australia = 29
4) Add both to DataModel (create connection only)
5) PowerPivot to create relationship on Country
6) DAX to return TotalEmployees[EmployeeCount] into RetentionCount
7) DAX to calculate RetentionCount[EmployeeCount] / TotalEmployees[EmployeeCount] = 0.48
8) Insert pivot table & chart
9) Convert values to display as %
9) Add slicer on Retention

Done.

I'm sure somewhere there is an easier way with less functions but that worked for me, hopefully that helps someone else