sum by customer RRS feed

  • Question

  • Hello. Why/how does this work? Used it calculate sum by group. 

    =CALCULATE([total sales],ALLEXCEPT(Table1,Table1[Customer]))

    Friday, December 23, 2016 9:39 PM


All replies

  • Hi, take a look at Marco Russo's great write-up on using CALCULATE with ALL, ALLEXCEPT, and VALUES. The examples in his blog should help you break this down.


    Essentially your function will evaluate the expression (due to the CALCULATE function) with a filter on all columns in Table1 except the Customer column (due to the ALLEXCEPT function).

    Sam Lester (MSFT)


    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, December 24, 2016 8:28 AM
  • Hi AlexMartini,

    In the formula, you use the ALLEXCEPT to filter the data. The ALLEXCEPT function is used to remove all the filter except Customer column. So it will calculate the total sales in each customer.

    Best Regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 26, 2016 3:22 AM
  • Thanks Sam, but if you put customers in row labels, that is a filter right? If you are taking the filter off of everything except for customer, wouldn't it just sum up ALL of the customers and the specific groups? 
    Monday, December 26, 2016 8:14 AM