Calculate Sum of column based on a column total by client and type of product

[replayyy]

Dear All,

I am trying to calculate how much revenue is gained through clients which buy a low annual quantity for specific type of products.

Enclosed I put 2 tables as a reference. 

Steps I am trying to do but doesnt work:

Total revenue type good= CALCULATE(SUM(Transaction Table[amount]),FILTER(Transaction Table[type] = "Revenue"), FILTER(Type clients[type] = "Good"))

Total qty sold = CALCULATE(SUM(Transaction Tabel[qty]),FILTER(Type clients[type} = "Good"))

IF(Total qty sold > 5, Total revenue type good,0) 

Now I would like to display the amount of revenue generated by "Good" clients while total quantity sold based on item level does not exceed 5 through the year.

Can anyone point me into the right direction?

Transaction Table

Client name	Date	type	Amount	qty	Itemcode
A	2019	Revenue	1000	1	Item 1
B	2019	Revenue	200	8	Item 1
C	2019	Revenue	300	8	Item 1
D	2019	Revenue	500	6	Item 1
E	2019	Revenue	800	1	Item 1
F	2019	Revenue	900	2	Item 1
A	2019	Revenue	500	6	Item 2
B	2019	Revenue	66	4	Item 2
C	2019	Revenue	500	2	Item 2
D	2019	Revenue	300	7	Item 2
E	2019	Revenue	400	10	Item 2
F	2019	Revenue	800	1	Item 2
A	2019	Revenue	500	6	Item 2
B	2019	Revenue	66	4	Item 2
C	2019	Revenue	500	2	Item 2
D	2019	Revenue	-300	7	Item 2
E	2019	Revenue	-400	-5	Item 2
F	2019	Revenue	800	1	Item 2
1	2019	Costs	-50
2	2019	Costs	-3
3	2019	Costs	-500
4	2019	Costs	-800
5	2019	Costs	-9000
6	2019	Costs	-10000
7	2019	Costs	-5000
8	2019	Costs	-400

Type client

Client name	Type
A	Good
B	Good
C	Good
D	Good
E	Bad
F	Bad

[harshnathani · Accepted solution]

Hi @replayyy ,

Try this

Measure = SUMX(FILTER(Revenue,RELATED('Client Category'[Type]) = "Good" && Revenue[qty] < 5),Revenue[Amount])

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[amitchandak]

@replayyy , use all expect and build this formula at the client level and use

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[replayyy]

Thnx Amitchandak, I have read the article and I am not sure how to incorporate the allexcept function. Care to elaborate a little?

Thank you!