cancel
Showing results for
Did you mean:
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

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

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
Member

## Re: Filter based list to use for calculation

 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.

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

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

Member

## Re: Filter based list to use for calculation

whats cust slicer here

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

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