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

## Please help to solve the measure

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

2 REPLIES 2
Solution Sage

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

Regular Visitor

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

## Helpful resources

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### Microsoft named a Leader in The Forrester Wave

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

#### Power BI Dev Camp - September 30th, 2021

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

#### Microsoft Learn

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

Top Solution Authors
Top Kudoed Authors