Forum Discussion
Subsets with measures
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
Thanks for the additional info. That does not look like a complete expression. In any case, you need to filter down to the Brands (for that visual) that have no returns. Here is a modified measure that should do that. I don't know which table has your Brand column, so you'll need to replace Table with that name.
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", FILTER ( ALLSELECTED ( Table[Brand] ), CALCULATE ( SUM ( 'Sales Order Detail'[Custom Quantity] ), 'Order Type'[Order Type Transaction Group] = "Return/Dump" ) > 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
Yes, Return Quantity is a measure as well.