cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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!!
1 ACCEPTED SOLUTION
Super User

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.

3 REPLIES 3
Super User

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.

New Member

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.

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors