Forum Discussion

sgk123's avatar
sgk123
Frequent Visitor
9 years ago
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 ...
Need Help
  • Sean's avatar
    Sean
    9 years ago
    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's avatar
    Sean
    9 years ago
    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
)
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago
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's avatar
    sgk123
    Frequent Visitor
    9 years ago

    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

    LocationItemDateWeekAmountWeeklyAmount
    AX1/1/2017155
    BZ1/1/201711010
    AX1/2/2017255
    AZ1/2/201721015
    BY1/2/201722020
    AZ1/2/201722015

     

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

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      9 years ago
      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
)