Forum Discussion
Solved
Weekly Totals
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 ...
- 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 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 )
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
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
)