Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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