Exclude value using Table vaule

tusharmehta

New Member
Joined
May 12, 2014
Messages
34
Hello Everyone,

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

ItmTypeItmCodeQty Amount
GLITM00011 500.000
DIITM00021 1,500.000
DIITM00031 1,800.000
STITM00041 200.000
GLITM00051 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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,250
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]
 

tusharmehta

New Member
Joined
May 12, 2014
Messages
34
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))


TotalCl
Row LabelsDIGLSTGrand Total
ITM0001500500
ITM000215001500
ITM000318001800
ITM0004200200
ITM0005500500
Grand Total330010002004500

<tbody>
</tbody>


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


TotalExcludeCL
Row LabelsDIGLSTGrand Total
ITM000110001000
ITM000230003000
ITM000336003600
ITM0004400400
ITM000510001000
Grand Total660020004009000

<tbody>
</tbody>

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


TotalIncludeCl
Row LabelsDIGLSTGrand 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.

https://drive.google.com/file/d/0B_c8gU3TDT4rZmdPenp0QjNNVEU/view?usp=sharing

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

Thanks,
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

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

tusharmehta

New Member
Joined
May 12, 2014
Messages
34
Thanks guys It is working as expected and I had learn some advance use of sumx and calculate
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,913
Members
414,110
Latest member
docops

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top