Forum Discussion

roelf's avatar
roelf
Icon for Helper II rankHelper II
7 years ago
Solved

SUM Based on values in another column

Hi

 

Probably a very basic question, but I"m stuck.

 

My table looks like this

 

Customer  Week Sale

A                  1       100

B                  1        200

A                  1         50

B                  1         10

A                  2        50

B                  2        150

A                  2         50

 

I want to be able to SUM sales per customer, per week

 

 

I want my table to look like this afterwards

Cust  Week   Sale Total

A        1         100  150

B        1         200  210

A        1         50   150

B        1         10    210

 

I would expect to use a SUMX, but I have not been able to understand how that works... Any help will be appreciated.

 

Thanks

R

Need Help
  • affan's avatar
    affan
    7 years ago

    Hi roelf,

     

    A simple SUM with filter would resolve the requirement

     

    Total = CALCULATE(SUM('Table'[Sale]),ALLEXCEPT('Table','Table'[Customer],'Table'[Week]))

     

     

     

     

    Please mark this post as an accepted solution if this helped you.

     

    Regards,

    Affan

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    In a Matrix visual, drag all columns to the Row labels section.  Write this measure

     

    =CALCULATE(SUM(Data[Sale]),ALL(Data[Week])

     

    Hope this helps.

  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi roelf,

     

    Does that make sense? If so, kindly mark my answer as a solution to close the case.

     

    Regards,
    Frank

5 Replies

  • affan's avatar
    affan
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi roelf,

     

    A simple SUM with filter would resolve the requirement

     

    Total = CALCULATE(SUM('Table'[Sale]),ALLEXCEPT('Table','Table'[Customer],'Table'[Week]))

     

     

     

     

    Please mark this post as an accepted solution if this helped you.

     

    Regards,

    Affan

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    In a Matrix visual, drag all columns to the Row labels section.  Write this measure

     

    =CALCULATE(SUM(Data[Sale]),ALL(Data[Week])

     

    Hope this helps.

      • roelf's avatar
        roelf
        Icon for Helper II rankHelper II
        7 years ago

        Thanks all for the assistance !

         

        Regards

        Roelf

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi roelf,

     

    Does that make sense? If so, kindly mark my answer as a solution to close the case.

     

    Regards,
    Frank