I have a table with two dimensions, store and year. I want to take a count of distinct employees at each store and calculate the percentage of employees at each store segmented by year. So in the table below, in 2018 store A had 6 of the total 17 distinct employees, which comes out to about 35%. I tried using the following expression: (Count(DISTINCT EMPLOYEES)/Count(TOTAL DISTINCT EMPLOYEES)), but the denominator is incorrect here because it returns a count of total distinct employees across all years.
| Store |
Year |
Count(Distinct Employees) |
**Desired Calculation** |
| A |
2018 |
6 |
6/17 = .35 |
| B |
2018 |
4 |
4/17 = .24 |
| C |
2018 |
7 |
7/17 = .41 |
| A |
2019 |
8 |
8/27 = .30 |
| B |
2019 |
7 |
7/27 = .26 |
| C |
2019 |
12 |
12 / 27 = .44 |
| A |
2020 |
5 |
5 / 26 = .19 |
| B |
2020 |
7 |
7 / 26 = .27 |
| C |
2020 |
14 |
14 / 26 = .54 |
