Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I would like to show percent by department which I have data set for expample below - Name and department. Then I have a reference table how many people by department. I already linked these two tables together. However; I can't find the way to write the calculation to DISTINCTCOUNT name / by number of people in that department (from ref table)
Thank you so much.
Table1: data
Table2: reference
Solved! Go to Solution.
Oh ok, so if I understand correctly, in Table1 you can have "Name" repeated?
Then your measure could be something like this:
Percentage of department =
CALCULATE(
COUNTA(Table1[Name])/SUM(Table2[Total no. by department]),
Table2[department]=SELECTEDVALUE(Table1[department])
)
Assuming you want to show percentage of total for each department, and each "Name" has a number, amount or quantity, for example Table1[Name Quantity], the measure could be:
Percentage of department =
CALCULATE(
SUM(Table1[Name Quantity])/SUM(Table2[Total no. by department]),
Table2[department]=SELECTEDVALUE(Table1[department])
)
Alternatively, you can add it as a calculated column to Table1 like this:
Percentage of department =
DIVIDE(Table1[Name Quantity],RELATED(Table2[Total no. by department]),0)
Thank you so much for your help. Sorry to ask more question. Name is text input and may need to distinctcount by department, how to write that to build the Table1[Name Quantity]
Oh ok, so if I understand correctly, in Table1 you can have "Name" repeated?
Then your measure could be something like this:
Percentage of department =
CALCULATE(
COUNTA(Table1[Name])/SUM(Table2[Total no. by department]),
Table2[department]=SELECTEDVALUE(Table1[department])
)
Thank you so much. I do appreciate your help
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |