Forum Discussion
Filter based list to use for calculation
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...
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
4 Replies
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.
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
Super User
Try this measure
Total Discount = CALCULATE([Total Discount Amount], ALLEXCEPT('Cust Slicer', 'Cust Slicer'[Cust Id]) )
