Exclude value using Table vaule

[tusharmehta]

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.

 

[Matt Allington]

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]

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.

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,

 

[Matt Allington]

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

 

[FDibbins]

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

 

[Matt Allington]

Calculate is a DAX formula which is part of Power Pivot

 

[tusharmehta]

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