Create a group based on to columns

[morgancampbell]

I have data which is layed out like below:

Name	Date	Week	Call Time (Hours)
Person 1	11/09/20	37	3.4
Person 2	11/09/20	37	2.4
Person 3	11/09/20	37	4.3
Person 1	10/09/20	37	3.1
Person 2	10/09/20	37	1.4
Person 3	10/09/20	37	2.3
Person 1	09/09/20	37	4.7
Person 2	09/09/20	37	3.9
Person 3	09/09/20	37	2.6
etc	etc	etc	etc

And I'm looking to create an additional colum that sums up the total call time per person per week.

The problem is I can't figure out how to create either a column or group that allows me to do this. So how would I go about this?

Thanks

[v-yingjl · Accepted solution]

Hi @morgancampbell ,

Based on your description, you can create this calculated column:

Sum =
CALCULATE (
    SUM ( 'Table'[Call Time (Hours)] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Name] = EARLIER ( 'Table'[Name] )
            && 'Table'[Week] = EARLIER ( 'Table'[Week] )
    )
)

For Person 1:

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

[amitchandak]

@morgancampbell ,

Create columns like

week Year = year([Date])*100 + WEEKNUM([Date],2) //use 1 for sunday week

week sum person wise = sumx(filter(Table, [Week year] =earlier([Week year]) && [person] =earlier([person]) ),[Call Time (Hours)])

Then create a bucket on week sum person wise