Forum Discussion
Subsets with measures
I want to calcualte only sum Shipment Quantity when there was a return.
| Customer | Shipment Quantity | Return Quantity | Return % |
| A | 50 | 35 | 70% |
| B | 100 | 5 | 5% |
| C | 340 | 0 | 0% |
| D | 270 | 25 | 9.25% |
| Total | 760 | 65 | 8.55% |
So Overall Shipment Quantity is 760 (50+100+340+270)
Overall Return is 65 (35+5+25)
So return % is: 65/760 = 8.55% which is easy to calculate.
Now, the problem is: If I only want to calculate % of shipment, if there is a return, then it will be:
420/760 = 55% (exclude the customer which didn't have any returns).
where 420 = 50 +100 +270 (A, B and D)
overall shipment 760 = 50 + 100 + 340 + 270
How do I calculate this?
Here are my formulas:
Shipment Quantity =
VAR TotalSales =
CALCULATE (
SUM ( 'Sales Order Detail'[Custom Quantity] ),
'Order Type'[Order Type Transaction Group] = "Sales"
)
RETURN
TotalSalesReturn Quantity =
VAR TotalReturns =
CALCULATE (
SUM ( 'Sales Order Detail'[Custom Quantity] ),
'Order Type'[Order Type Transaction Group] = "Return/Dump"
)
RETURN
TotalReturnsReturn % =
VAR TotalReturnDump =
CALCULATE (
SUM ( 'Sales Order Detail'[Custom Quantity] ),
'Order Type'[Order Type Transaction Group] = "Return/Dump"
)
VAR TotalSales =
CALCULATE (
SUM ( 'Sales Order Detail'[Custom Quantity] ),
'Order Type'[Order Type Transaction Group] = "Sales"
)
RETURN
ABS ( DIVIDE ( TotalReturnDump, TotalSales, BLANK () ) )
I created this measure as well:
Brand Product Line Return Flag =
IF ( ISBLANK ( [Return Quantity] ), "NO", "YES" )
But I can't use this in Filter function as this is a measure.
Any help?
Like in the picture below,
First table gives me all the Shipments and their Return values.
2nd table gives me only Shipments where Returns happen.
Both the tables have same Return quantity. Only Shipment numbers are changing due to our logic.
And I want to calculate 3,54,539 /1,078230 and so on and so forth....
Thanks,
Ritesh
16 Replies
- mahoneypatMicrosoft Employee
Please try an expression like this:
New % Measure = VAR __totalshipment = SUM ( Table[Shipment Quantity] ) VAR __withreturns = CALCULATE ( SUM ( Table[Shipment Quantity] ), Table[Returned] > 0 ) RETURN DIVIDE ( __withreturns, __totalshipment )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- Ritesh_AirPost Patron
New % Measure = VAR _totalShipment = CALCULATE ( SUM ( 'Sales Order Detail'[Custom Quantity] ), 'Order Type'[Order Type Transaction Group] = "Sales" ) VAR __withreturns = CALCULATE ( SUM ( 'Sales Order Detail'[Custom Quantity] ), 'Order Type'[Order Type Transaction Group] = "Sales", ( 'Sales Order Detail'[Custom Quantity] ), 'Order Type'[Order Type Transaction Group] = "Return/Dump" ) > 0 RETURN DIVIDE ( __withreturns, _totalShipment )No luck.
- mahoneypatMicrosoft Employee
That is not the same as the measure I suggested. Your example had a column for return quantity. I don't see that column mentioned in your adapted measure. There needs to be a filter on the column to exclude 0 values. Is Return Quantity a measure?
Regards,
Pat