Forum Discussion

ThomasSan's avatar
ThomasSan
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper IV
3 years ago

Hi tamerj1 ,

thank you for your reply. Unfortunately, I get a (Blank) returned which I do not quite understand. My filter is using Orders[Customer] so I do not quite know why it won't respond to SELECTEDVALUE(Orders[Customer]). Naturally, I checked the data and there are other sales by customers that are not C1 in that year. Do you maybe know where else I could check or what else might help?

 

Edit:
I realize that the problem is that in my larger data set, I have more than two customers. I was not aware that my data extract with only two customers may pose an issue here. Do you know how to transform the SELECTEDVALUE(Orders[Customer]) bit to a list? I have already unsuccessfully tried not(Orders[Customers] in {SELECTEDVALUE(Orders[Customer])})

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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
    Icon for Helper IV rankHelper 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
      Icon for Community Champion rankCommunity 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] ) )
    )
)