Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
@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:
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 |
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |