How to summarise a measure, currently not summing in matrix subtotals

[Haris-Khan]

Hi, 

I have created a measure to calculate the number of weeks colleagues are behind on timesheets:

Weeks Overdue = CALCULATE(DATEDIFF(LASTDATE(Timesheet_Report[Week_Commencing_Date]),TODAY()-WEEKDAY(TODAY(),1),WEEK),NOT(ISBLANK(Timesheet_Report[Date_Committed])))

So the measure takes the number of weeks difference between their last sumbmission and the end of the previous week we are currently in. 

The matrix I have includes the teams and colleague names (belong to those teams) as rows and then the measure as the values - which correctly calculates the number of weeks they are behind on timesheets, but does not summarise the total number of weeks each team is behind. 

Do you know how I can chnage my DAX code to make this happen or what other steps I could take so that the matrix subtotals (and grand total) sum correctly?

Thanks!!

[jdbuchanan71 · Accepted solution]

Try it like this.

Weeks Overdue Totaled = 
SUMX ( Contacts_Table, [Weeks Overdue] )

You use your existing measure and iterate over the contacts table so the totals add up correctly.

[Haris-Khan]

The Timesheet_Report contains Contact ID, which have a relationship with a seperate data table that includes the ID and contact name. The Contact ID then has a second relationship with another data table with includes the contact ID and team ID and the the team ID is joined to another dataset that has the team ID and the team name. How do you propose the SUMX code looks? Because the numbers I want to summarise come from the difference between the last submission and the end of the previous week. I tried...

SUMX(Timesheet_Report,DATEDIFF(LASTDATE(Timesheet_Report[Week_Commencing_Date]),TODAY()-WEEKDAY(TODAY(),2),WEEK))

No luck. 

[jdbuchanan71]

@Haris-Khan 

How is your data laid out?  Do you have your teams and members in a separate table that is linked to the Timesheet_Report?  You will need to used a SUMX with your measure to calculate the correct subtotals.