Show categories with zero value, but only for selected parent category (sample Pbix included)

[wilson_smyth]

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.

[themistoklis]

@wilson_smyth

Change the measure %Form Submitted to this one:

%Form Submitted = CALCULATE([#FormSubmitted], ALL(EmpFact[Employee Category]))/ CALCULATE([TotalEmployees], ALL(EmpFact[Employee Category]))

[wilson_smyth]

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% 

[themistoklis · Accepted solution]

@wilson_smyth

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"

[wilson_smyth]

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.

[themistoklis]

@wilson_smyth

Possibly but i have never tried to see if i can get the same results by modifying a DAX formula