Forum Discussion

ThomasSan's avatar
ThomasSan
Helper IV
3 years ago
Solved

Use double filter when calculating a sum

Hi everyone,    I have the following table: Order ID Order Date Order Value Customer O1 01.01.2021             248,00 € C1 O16 19.01.2022                40,00 € C1 O15 12...
CALCULATE
Filter
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, ThomasSan 

    You can replace the [Rest] measure :

    Rest = 
VAR _slice = VALUES('Sheet1'[Customer])
return 
SUMX( FILTER(ALL(Sheet1) , YEAR('Sheet1'[Order Date]) =2022 && not 'Sheet1'[Customer] in _slice ),'Sheet1'[Order Value])

    The result is as follows:

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

ThomasSan's avatar
ThomasSan
Helper IV
3 years ago

Thank you for your response, v-yueyunzh-msft !

Unfortunately, in my description above, I failed to mention that I have more than two customers. Do you happen to know how to successfully transform the "<> SELECTEDVALUE('Sheet1'[Customer]) " bit into a list?

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi, ThomasSan 

You can replace the [Rest] measure :

Rest = 
VAR _slice = VALUES('Sheet1'[Customer])
return 
SUMX( FILTER(ALL(Sheet1) , YEAR('Sheet1'[Order Date]) =2022 && not 'Sheet1'[Customer] in _slice ),'Sheet1'[Order Value])

The result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly