Help with a table - excluding specific columns from slicer filtering

[mfarina]

I have a Matrix that looks something like below - it is used to track the number of training enrollments & completions by each department, relative to the department's size. (I'm using a Matrix because there are some other measures included that aren't relevant, so it's effectively simplified as a table below).

A slicer is used to specify the training(s) being measured, so my main issue is that the Department headcounts will only show the number of users in a department with the training on their transcript (so if Sales has 20 people but only 4 are registered, the headcount will show 4).

I tried creating a data table that contains the list of departments in column 1, and the following formula in column 2 to show the total headcounts of each department:

COUNTROWS(FILTER(ALL(Users),Users[Department]=Departments[Department])

and it's working in the data table, but not in the matrix. What happens in the matrix is, for each Department, all the values in column 2 show up - so it would say: Sales | 20 / 6 / 32 / 15, Training | 20 / 6/ 32/ 15, etc. How can I get the department's total headcount, unaffected by the number of people enrolled/completed in each department, and (ideally) the enrollments and completions as percentages of the department headcount?

[v-yulgu-msft]

@mfarina

Hi mfarina,

I am confused about your real requirement according to current description. Based on my assumption, I created two data tables like below:

User Table

Department Tbale

After creating a one to many relationship between these two tables, I added a new measure to calculate the Department HeadCount. 

Department HeadCount = COUNTROWS(FILTER(Users,Users[Department]=RELATED(department[Department])))

It worked well both in matrix and table visualization. Does this meet your requirement? If I have something misunderstood, would you please share the .pbix file if possible.

Best regards,
Yuliana Gu

[BhaveshPatel]

The bottom part of your requirement is bit confusing. Why do you need to write such a equal condition when you can form a relationship between tables.

If you can post your sample data with expected output, we will get an idea of what are you trying to achieve.

Thanks & Regards,

Bhavesh