cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ganesanr30
Regular Visitor

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
daxer
Solution Sage
Solution Sage

@ganesanr30 

 

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-...

 

 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.