Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have data showing employees for each manager, and if the employees have submitted a form.
Im trying to show, on a bar chart, for each manager, the percentage of employees in a category that have submitted.
I need to show 0% also.
The challenge i am facing is that when i show categories with 0%, i get all categories back, not just categories for the manager specified in the slicer.
e.g.
Manager 100 has employees in category A, B & C, but not in category D or E.
Category B has 0% submission for manager 100.
Ive created a measure to show percentage of employees who have submitted for each manager. Note that Category D & E appear even though manager A is selected in the slicer.
%Form Submitted = if( [#FormSubmitted]>0 ,[#FormSubmitted]/[TotalEmployees] ,0) #FormSubmitted = CALCULATE(SUM(EmpFact[EmployeeID]), filter(EmpFact, EmpFact[EmployeeFormSubmitted] = "Y")) TotalEmployees = sumx(EmpFact, EmpFact[EmployeeID])
How would I just display the categories for the specific manager, in this case A, B & C, but ensuring i can still see Categories with 0%?
The example with the sample data is available here
Appreciate any help and expertise.
FYI - Although not necessary here, I need to split the data out into dimension tables for other measures in the real report.
Solved! Go to Solution.
Click on the graph and on Axis section on Visualisations Pane do right click on the down arrow on "Employee Category"and select "Show Items with no data"
Change the measure %Form Submitted to this one:
%Form Submitted = CALCULATE([#FormSubmitted], ALL(EmpFact[Employee Category]))/ CALCULATE([TotalEmployees], ALL(EmpFact[Employee Category]))
Hi.
that wont work unfortunately, as now Category B does not show for Manager 100.
All that should show for Manager 100 is:
Category A: 100%
Category B: 0%
Category C: 100%
Click on the graph and on Axis section on Visualisations Pane do right click on the down arrow on "Employee Category"and select "Show Items with no data"
Excellent, i did not know this option existed! Thank you for your expertise!
Out of curosity, for educational purposes, id still like to know if this is solvable using just measures and filters.
Possibly but i have never tried to see if i can get the same results by modifying a DAX formula
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |