Create Sum based on Two Fields

[Razorbx13]

I need to sum a measure based on a group of two fields.  Example:  Need to create a measure that totals the hours based on a grouping of Employee and Week Start.  Searching has not helped.  I know how to do a SUM for the whole table, but need to do a SUM based on the combination of Employee and Week Start.  Do not want to create a group table as I need to see the detail as well.  TIA

[amitchandak]

Create a date table join it with your table.

Have a week start in date table

Week Start date = 'Date'[Date]-1*WEEKDAY('Date'[Date])+1

Use that in the table as a group by.

[Razorbx13]

I do have it joined to a date table.  But if I do a group by will I lose the underlying detail?  What I am trying to do is a filter in a report based on a subtotal field, but need to expand that to see the underlying details.  Trying to figure out how this can be done as the filter actually filters the details, not the subtotal.  Hopefully I am explaining this well.

[dax]

Hi @Razorbx13 ,

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Razorbx13]

Basically what I am trying to do is filter by a subtotal in a report.  Here is an example of some data.  I have employees that work on different clients during the week.  I have created a report that shows me the totals by Employee by Week Start and I can see that Employee 1 worked over 40 hours the first week, Employee 3 worked over 40 hours both weeks.  If I put a filter on HOURS > 40 well Employee 1 is filtered out.  I will basically see only one record.  I actually need to see all Employees that have over 40 hours in total for the week by Employee, then show the detail below that.  So using the below, Employee one would NOT be filtered out when I put the Hours > 40 filter on.  It would still show as would the detail.  I was hoping that the HOURS filter would be on the Subtotal since I was showing that in report, but the minute I brought the Client Name into the report in showed not just the subtotal but the next level of granularity and thereby removed Employee 1.  Again, maybe there is a way to set a field or something that states something like "If total hours of Employee and Weeks Start >40 then Yes, else No".  However, was hoping to provide the capability of the user to set a filter.

Employee            Week Start           Hours    

Employee 1         01/01/2020           38

Employee 1         01/01/2020            8

Employee 1         01/08/2020           40

Employee 2         01/01/2020           40

Employee 3         01/01/2020           43

Employee 3         01/08/2020           25

Employee 3         01/08/2020           25

Employee 4         01/01/2020           40 

[dax]

Hi @Razorbx13 , 

If you want to show details, you could "Don't summarize" of field, but in table, it can't show duplicated rows, so I think you need to create an index column for it. You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Razorbx13]

Thanks DAX, it got the calculation working.  Next question, when I move the new Measure to Values and then filter on it to show only Employees where this measure is > 55 hours for a given Week, why would it not filter?  It seems to be filtering on the TOTAL for ALL WEEKS, not a given intersection of an Employee and Week Start.  

[dax · Accepted solution]

Hi @Razorbx13 ,

Could you please tell us if your question has been resolved. If so, in order to close the thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dax]

Hi @Razorbx13 , 

At first, the measure is based on employ and start week, it calculate sum of this . So when you filter it, it will show the detailed row which belongs to "employ and start week" group >40

In addition, when you change the condition in filter, you need to click "apply filter" again. 

If this is not what you want, please correct and inform me detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.