topic Re: Please help to solve the measure in DAX Commands and Tips

Please help to solve the measure

Anonymous — Sat, 12 Jun 2021 03:24:43 GMT

Hi,

I have a three tables like Product Master,Sales QTY,Return Transfer Product
Product Master

Sales QTY

Return Transfer Product

I am trying to achieve is find the total Return as per the sales qty in measure .Using the condition like If the Transfer Product Code in Sale Qty [ProductCode] and Transfer Product[Country] in Sale Qty [Country] alone need to find the Sales Qty then i need to find the Total Return QTY - Sale QTY for the particular condition mention before
Eg :
If the ProductCode is 25 and Country is US (Return is 30 Qty Date :06-05-2021) and Transfer Product Code is 116 and County is US (Sale is 20 Qty Date :06-05-2021)
I am trying this measure to achieve it

Rotation_return_qty =
var _return=CALCULATE(sum('Sale Qty'[QTY]),'Sale Qty'[Type]="Return",'Sale Qty'[Product Code] in VALUES('Return Transfer Product'[ProductCode]) && 'Sale Qty'[Country] in VALUES('Return Transfer Product'[Country]))
var _saleqty=CALCULATE(sum('Sale Qty'[QTY]),'Sale Qty'[Type]="Sale",'Sale Qty'[Product Code] in VALUES('Return Transfer Product'[Transfer Product Code]) && 'Sale Qty'[Country] in VALUES('Return Transfer Product'[Country]))
return (_return-_saleqty)

I got the result but acturally
if the product code is 25 (Return : 30 Qty) and Transfer Product is 116(Sale : 20 QTY) i need to substract the value in same line ...
i connected Product master[Product Code] to Sale Qty[Product Code] as Relationship..Pls kindly help me to solve this issue...Thanks in advance

Eg :

Product Code	Return	Rotation Return
25	30	10

Re: Please help to solve the measure

Anonymous — Tue, 15 Jun 2021 12:26:40 GMT

Anonymous

Sorry, but the description is very unclear (this is why you haven't received any answers yet). If you do want to get answers, you have to phrase the problem in a much clearer, understandable way.

A good place to learn about how to pose questions is here:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-p/1626726

Re: Please help to solve the measure

Anonymous — Tue, 15 Jun 2021 13:06:40 GMT

Ohh Really Sorry..... var Sales Qty= If the (SalesQty[Product Code]=Return Transfer Product[Transfer Product Code]) and (SalesQty[Country]=Return Transfer Product[Country]) {

return sum of SalesQty[Qty] where SalesQty[Type] is "Sale"

}
var Return Qty=

If the (SalesQty[Product Code]=Return Transfer Product[Product Code]) and (SalesQty[Country]=Return Transfer Product[Country]) {

return sum of SalesQty[Qty] where SalesQty[Type] is "Return"

}
var final result=Return Qty-Sales Qty

Eg : If the Product Code is 25 (Total ReturnQty is 30) and If the Transfer Product Code is 116 (Total SaleQty is 20) on the date (06-05-2021)

Need to Show the result in same Line

Product Code	ReturnQty	SaleQty	final result
25	30	20	10