Weekly Totals

[sgk123]

I need to create a column with weekly totals

I have different locations and different items selled on different days.

I need a column like below

Or else I need a column with weekly average values.

Like the first and second row should display an average of 7.5 and all other rows to display 15 as weekly average.

Can some one please help me with this

[Sean · Accepted solution]

CALCULATE ( SUM (Table[Amount]), ALLEXCEPT ( Table, Table[Week] ) )

You should add a YYYY-WW column and reference it in the formula not just the week number

[Sean · Accepted solution]

CALCULATE ( SUM (Table[Amount]), ALLEXCEPT ( Table, Table[Week] ) )

You should add a YYYY-WW column and reference it in the formula not just the week number

[sgk123]

I'm so sorry @Sean, I was little confused while asking the question.

It has to display based on the location, item and week - like below

Location	Item	Date	Week	Amount	WeeklyAmount
A	X	1/1/2017	1	5	5
B	Z	1/1/2017	1	10	10
A	X	1/2/2017	2	5	5
A	Z	1/2/2017	2	10	15
B	Y	1/2/2017	2	20	20
A	Z	1/2/2017	2	20	15

As 4 and 6 rows has same location, item and week, then it has to show the total

[Sean · Accepted solution]

Weeekly Amount =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Location], 'Table'[Item] )
    ),
    CALCULATE (
        COUNTA ( 'Table'[Item] ),
        ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Location], 'Table'[Item] )
    ),
    0
)