Forum Discussion
Use double filter when calculating a sum
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
Hi ThomasSan
please try
Gross Margin CY Other =
CALCULATE (
SUM ( OrderDetails[Unit Margin] ),
ALL (),
FILTER (
Orders,
YEAR ( Orders[Order Date] ) = 2022
&& Orders[Customer] <> SELECTEDVALUE ( Orders[Customer] )
)
)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])})ThomasSan
Please tryGross Margin CY Other = CALCULATE ( SUM ( OrderDetails[Unit Margin] ), ALL (), FILTER ( Orders, YEAR ( Orders[Order Date] ) = 2022 && NOT ( Orders[Customer] IN VALUE ( Orders[Customer] ) ) ) )
