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

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

ThomasSan 
Please try

Gross Margin CY Other =
CALCULATE (
    SUM ( OrderDetails[Unit Margin] ),
    ALL (),
    FILTER (
        Orders,
        YEAR ( Orders[Order Date] ) = 2022
            && NOT ( Orders[Customer] IN VALUE ( Orders[Customer] ) )
    )
)
ThomasSan's avatar
ThomasSan
Helper IV
3 years ago

tamerj1 

Unfortunately, it says "The function expects a table expression for argument '2', but a string or numeric expression was used." here

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    ThomasSan 
    Sorry a typo mistake. Missed the "S" in VALUE(S)

    Gross Margin CY Other =
CALCULATE (
    SUM ( OrderDetails[Unit Margin] ),
    ALL (),
    FILTER (
        Orders,
        YEAR ( Orders[Order Date] ) = 2022
            && NOT ( Orders[Customer] IN VALUES ( Orders[Customer] ) )
    )
)