Filter based list to use for calculation

[Anonymous]

Hi All,

I have scenario that I need to have sum(discount_amt) only for customers which has certain filter applied on it. I have query which gives me data customer wise discount applied., Now

1. I have to filter customers based on some other columns in same query and then 

2. Take thos ecustomers go to the all orders for those customers 

3. and then calculate discount amount for all orders.

Here in 1st step I may get 3 customers which has total(discoun_amt) as $100. But there may 10 orders for thsoe 3 customers which are getting filtered out due to filters in step 1.

Once I get 3 customers I need to clear the filters and goto all orders for those 3 customers and then sum(discount_amount) and show that as Promotion Discount.

Hope it helps...

[Anonymous]

Cust Id	Sale Id	Success	Yesterday	5in90	Disc Amount
123	111	Y	N	N	1000
234	222	Y	Y	Y	2000
123	333	Y	Y	Y	3000
456	444	Y	Y	N	4000
789	555	N	Y	N	5000

For Ex in Above tBale if I apply filter as Success=Y,Yesterday=Y and 5in 90 =Y I get below customers 

Cust Id	Sale Id	Success	Yesterday	5in90	Disc Amount
234	222	Y	Y	Y	2000
123	333	Y	Y	Y	3000

Then I need to clear all filters goto all sale IDs for Cust ID 234,123 which are below

Cust Id	Sale Id	Success	Yesterday	5in90	Disc Amount
123	111	Y	N	N	1000
234	222	Y	Y	Y	2000
123	333	Y	Y	Y	3000

and then do sum(discount_amount) which is $6000, I wanted to do this in DAX if possible.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use below measure to use the filtered result as the parameter to filter on original table:

Multi-filter result =
VAR filtred =
    CALCULATETABLE ( VALUES ( 'Sample'[Cust Id] ), ALLSELECTED ( 'Sample' ) )
RETURN
    SUMX ( FILTER ( ALL ( 'Sample' ), [Cust Id] IN filtred ), [Disc Amount] )

Regards,

Xiaoxin Sheng

[parry2k]

Try this measure 

Total Discount = CALCULATE([Total Discount Amount], ALLEXCEPT('Cust Slicer', 'Cust Slicer'[Cust Id]) )

[Anonymous]

whats cust slicer here