Average, percentage maximum of a Measure

[TeaPeas]

Hi, I've been googling and searching and basically going around in circles, thought I had it and then realized I didn't! 

I have a table, that very very similified is a list of names, departments, codes and dates. Every week someone should have at least one entry. I need to show the number of weeks submitted and the overall percentage completed.

Example Data:

Name	Department	Code	Week End Date
A	1	L700025000	03/06/2018
A	1	L700102000	03/06/2018
A	1	L700001000	06/05/2018
A	1	L700002000	13/05/2018
A	1	L700001000	20/05/2018
A	1	L700008000	03/06/2018
A	1	L700102000	06/05/2018
A	1	L700010000	13/05/2018
A	1	L700010000	20/05/2018
B	1	L803001000	06/05/2018
B	1	L803001000	13/05/2018
B	1	L803001000	27/05/2018
B	1	L803001000	03/06/2018
B	1	L803001000	06/05/2018
B	1	L803001000	13/05/2018
B	1	L803001000	27/05/2018
B	1	L803001000	03/06/2018
B	1	A000000000	20/05/2018
C	2	L800001000	06/05/2018
C	2	L800001000	20/05/2018
C	2	L800001000	27/05/2018
C	2	L800001000	06/05/2018
C	2	L800001000	20/05/2018
C	2	L800001000	27/05/2018

Example Outputs Required

Name	Weeks Submitted	Percentage Complete
A	4	80%
B	5	100%
C	3	60%

Department	Weeks Submitted	Percentage Complete
1	9	90%
2	3	60%

 (Department Value is total of the people working in the department/total possible if every submitted every week, so in this example there are 5 possible weeks, so as there are two people in Dept 1, you get 9/10, and only one in Dept 2 you get 3/5)

Now I have a measure that gives me the Weeks Submitted  - Submitted Weeks = DISTINCTCOUNT('TRaC Data'[Week End Date])

But as I've typed this post I've realised that this isn't working at department level, and is just giving me the maximum, not the total. 

There has to be a straightforward way of doing this that I'm completely overlooking? I'm completely DAX-Blind!

Please help...!

[Stachu · Accepted solution]

try these measures

Weeks Submitted = 
COUNTROWS(
    SUMMARIZE(
        'TRaC Data',
        'TRaC Data'[Department],
        'TRaC Data'[Name],
        'TRaC Data'[Week End Date]
    )
)

Percentage Complete = 
VAR DepNameWeek = CROSSJOIN(VALUES('TRaC Data'[Department]),VALUES('TRaC Data'[Name]),ALLSELECTED('TRaC Data'[Week End Date]))
RETURN
DIVIDE([Weeks Submitted],COUNTROWS(DepNameWeek))

[TeaPeas]

Thanks again.

If I were to have the average of Submitted Weeks for the department showing (Dep1 has 4.5 weeks, Dept2 has 3) who would I go about doing that?

Also, the %Complete is coming up with a strange total when I use it in a Matrix with a Grand Total - it's saying that the total is  7.97%, when it reality it is closer to 77%.  Any bright ideas?

🙂

[Stachu]

for the percentage please check this, should work better

Percentage Completed = 
VAR NrOfWeeks = COUNTROWS(ALLSELECTED('TRaC Data'[Week End Date]))
VAR NrOfNamesPerDep = COUNTROWS(SUMMARIZE('TRaC Data','TRaC Data'[Department],'TRaC Data'[Name]))
RETURN
DIVIDE([Weeks Submitted],NrOfWeeks*NrOfNamesPerDep)

for the average submitted weeks

Avg. Submitted Weeks = AVERAGEX(SUMMARIZE('TRaC Data','TRaC Data'[Department],'TRaC Data'[Name]),[Weeks Submitted])

[TeaPeas]

You are a beautiful, beautiful person and I can even understand the code. If you hadn't guessed, it works perfectly 🙂 I wish I had joined and asked when I first had this problem a week ago...there again I did learn a lot by continually going in circles and finding out what I had done wrong 🙂

Thank you so much! 

[TeaPeas]

Thank you so much! I could kiss you 😁

[Stachu]

glad it worked 🙂