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%...
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
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.
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
Sorry, for the confusion but the return quantity is calculated like this:
( 'Sales Order Detail'[Custom Quantity] ), 'Order Type'[Order Type Transaction Group] = "Return/Dump"Bascially, 1 filter is for Sales, 1 is for "Return/Dump" using the same "Order Type Transaction Group.
Do you think the solution could be done with calculated table? I have never used it but looking into it.
Thanks,
Ritesh
