Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Please help to solve the measure

Hi,

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

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 CodeReturnRotation Return
253010

 

 

2 REPLIES 2
Anonymous
Not applicable

@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

 

 

Anonymous
Not applicable

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 CodeReturnQtySaleQty final result
25302010

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors