Filter

[Anonymous]

Ik heb een tabel met:

datum, naam medewerker, roosteruren, waarvan uren verlof, uren ziek, uren gewerkt. 
nu wil ik een DAX functie maken om het percentage te berekenen van aantal medewerkers die in de geselecteerde periode niet ziek zijn geweest (som van de kolom waarde ziek is 0) ten opzichte van alle medewerkers. 

wie kan mij hier bij helpen? 

[Anonymous]

This is the data.

Date	Name	Scheduled working time	leave hours	hours of illness	working hours
15-5-2022	employee 1	8	0	0	8
16-6-2022	employee 1	8	0	0	8
17-5-2022	employee 2	8	8	0	0
18-5-2022	employee 3	8	0	8	0
19-6-2022	employee 3	8	0	0	8
20-6-2022	employee 4	8	0	0	8
21-6-2022	employee 5	8	0	0	8
22-6-2022	employee 2	8	0	8	0
23-6-2022	employee 5	8	0	0	8
24-6-2022	employee 6	8	0	0	8
25-5-2022	employee 5	8	0	8	0
26-6-2022	employee 6	8	0	0	8
27-6-2022	employee 6	8	0	0	8
28-5-2022	employee 4	8	0	0	8
29-5-2022	employee 6	8	0	0	8

The outcome should be:

nr. employee not haven been ill at all in june: 5 (83,3% of total employee's)

nr. employee not haven been ill at all in may: 4 (66,7% of total employee's)

nr. employee not haven been ill at all: 3 (50,0% of total employee's)

name            june   may    total

employee 1	0	0	0
employee 2	8	0	8
employee 3	0	8	8
employee 4	0	0	0
employee 5	0	8	8
employee 6	0	0	0

[tamerj1 · Accepted solution]

@Anonymous 
Appologies for the late reply. Here is a sample file with the solution https://we.tl/t-9aEQxioBnY

Hope this is what you're looking for

Haven't Been Ill At All Count = 
VAR TotalCount = DISTINCTCOUNT ( Data[Name] )
VAR IllCount = CALCULATE ( DISTINCTCOUNT ( Data[Name] ), Data[hours of illness] > 0 )
VAR NotIllCount = TotalCount - IllCount
VAR NotIllPercent = FORMAT ( DIVIDE ( NotIllCount, TotalCount ), "Percent" )
RETURN
    IF (
        Not ISBLANK ( TotalCount ),
        NotIllCount & " (" & NotIllPercent & ")"
    )

[Anonymous]

thank you very much for this solution. It works perfectly. 

This will help me also with writing other functions. 

[tamerj1]

Hi @Anonymous 
Please share sample file.