cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srlabhe123 Member
Member

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...

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter based list to use for calculation

Hi @srlabhe123,

 

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] )

12.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
4 REPLIES 4
Highlighted
srlabhe123 Member
Member

Re: Filter based list to use for calculation

Cust IdSale IdSuccessYesterday5in90Disc Amount
123111YNN1000
234222YYY2000
123333YYY3000
456444YYN4000
789555NYN5000

 

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

 

Cust IdSale IdSuccessYesterday5in90Disc Amount
234222YYY2000
123333YYY3000

 

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

 

Cust IdSale IdSuccessYesterday5in90Disc Amount
123111YNN1000
234222YYY2000
123333YYY3000

 

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

Super User
Super User

Re: Filter based list to use for calculation

Try this measure 

 

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




Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




srlabhe123 Member
Member

Re: Filter based list to use for calculation

whats cust slicer here

Community Support Team
Community Support Team

Re: Filter based list to use for calculation

Hi @srlabhe123,

 

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] )

12.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |