# Exclude value using Table vaule

#### tusharmehta

##### New Member
Hello Everyone,

I have situation where I want to exclude selected itmtype using table value.

 ItmType ItmCode Qty Amount GL ITM0001 1 500.000 DI ITM0002 1 1,500.000 DI ITM0003 1 1,800.000 ST ITM0004 1 200.000 GL ITM0005 1 500.000

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

----------------
Exclude Table
----------------

 ItmType GL ST

<colgroup><col></colgroup><tbody>
</tbody>

I would like to calculate amount sum using Exclude Table value, it means amount sum will include for ItmType = "DI".

I have tried:

DocTot:=SUMX(FILTER('Transaction','Transaction'[ItmType]<>"GL"),'Transaction'[Amount])

In above mentioned example I can get value but it will be fixed so I am trying to crate table so it will be dynamic.

DocTot1:=CALCULATE(SUM([Amount]),ALLEXCEPT('Exclude','Exclude'[ItmType]))

In above mentioned calculate error I am getting error, so suggestion will be highly appreciate.

Thanks.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
One way to do this is as follows.
1. Calculate the total
2. Calculate the total for the exclusions
3. Subtract the total in 1 from the total exclusions in 2 to give you the answer.

Total:=calculate(sum(transaction[amount]), all(exclude))
Total Exclude:= sumx(exclude, calculate(sum(transaction[amount])))
Total include:= [total] - [total exclude]

Hello Matt Allington

I had looked into your suggestion and found that something is wrong because result is not as per requirement.

--------------------------
First Formula result:
--------------------------
Total:=calculate(sum(transaction[amount]), all(exclude))

 Total Cl Row Labels DI GL ST Grand Total ITM0001 500 500 ITM0002 1500 1500 ITM0003 1800 1800 ITM0004 200 200 ITM0005 500 500 Grand Total 3300 1000 200 4500

<tbody>
</tbody>

---------------------------
Second formula result:
---------------------------
Total Exclude:= sumx(exclude, calculate(sum(transaction[amount])))

 TotalExclude CL Row Labels DI GL ST Grand Total ITM0001 1000 1000 ITM0002 3000 3000 ITM0003 3600 3600 ITM0004 400 400 ITM0005 1000 1000 Grand Total 6600 2000 400 9000

<tbody>
</tbody>

-------------------------
Third formula result
-------------------------
Total include:= [total] - [total exclude]​

 TotalInclude Cl Row Labels DI GL ST Grand Total ITM0001 -500 -500 ITM0002 -1500 -1500 ITM0003 -1800 -1800 ITM0004 -200 -200 ITM0005 -500 -500 Grand Total -3300 -1000 -200 -4500

<tbody>
</tbody>

My observations:

Due to some reason in formula 1 and two no value is getting exclude
Second formula total value is double means sum is getting twice.
Because of this final total is also minus.

I am attaching the url for file in which I had work on your suggestions so please look into it.

Thanks,

You haven't joined your tables in the data model. Join Transaction[ItemType] to Exclude[ItemType] and it will work

Unless I am mistaken, =calculate() is not a native excel function. Is this a UDF?

Calculate is a DAX formula which is part of Power Pivot

Thanks guys It is working as expected and I had learn some advance use of sumx and calculate

Replies
4
Views
617
Replies
1
Views
1K
Replies
3
Views
3K
Replies
1
Views
775
Replies
2
Views
185

1,211,434
Messages
6,101,836
Members
447,758
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back