How to sum a table with filter and conditions

[haoqizha]

Hi,

I have got a table as below:

Client	Product_Name	Date	Sales	QTY
A	APPLE	07/08/2022	$100	30
B	APPLE	07/08/2022	$200	20
A	BANANA	06/08/2022	$50	20
B	BANANA	06/08/2022	$40	25
A	CHERRY	05/04/2022	$30	30
B	MELON	03/04/2022	$20	30

The expected result is to create:

Client	Product_NAME	Date	Total sales by Product	Weight of client
A	APPLE	07/08/2022	$300	=100/300
B	APPLE	07/08/2022	$300	=200/300
A	BANANA	06/08/2022	$90	=50/90
B	BANANA	06/08/2022	$90	=40/90
A	CHERRY	05/04/2022	$30	=30/30
B	MELON	03/04/2022	$20	=20/20

I can't figure out how to let DAX (new measure) to calculate "Total sales by Product", that is supposed to sum by the same Product_Name across all Client within a same Date. Suppose once this is done, the Weight of client part should be fairly simple.

Thanks!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Sales all by product: = 
CALCULATE ( [Sales measure:], ALLEXCEPT ( Data, Data[Product_Name] ) )

[haoqizha]

Apologize for the bad formating. Date ends with 2022, so "300, 300, 90" etc are the sales figure.