Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Post Patron
6 years ago

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%...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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

 

 

 

Ritesh_Air's avatar
Ritesh_Air
Post Patron
6 years ago

mahoneypat 

 

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

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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

    • Ritesh_Air's avatar
      Ritesh_Air
      Post Patron
      6 years ago
      New % Measure 2 = 
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 ( 'Product'[Product Marketing Brand] ),
            CALCULATE (
                SUM ( 'Sales Order Detail'[Custom Quantity] ),
                'Order Type'[Order Type Transaction Group] = "Return/Dump"
            ) > 0
        )
    )
RETURN
    DIVIDE ( __withreturns, _totalShipment )

       

      mahoneypatIt comes blank.

       

       

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        Ok.  Thanks for trying it.  I am obviously missing something about your model.  One thing I did notice is that your original post had Customer in the example table and it was a later pic that had Brand.  If those are different columns, it is the Customer column that should be in the ALLSELECTED().  You can try one more thing by replacing the FILTER() part with the below code.  It uses your Return Quantity measure too.

         

        FILTER (
            ALLSELECTED ( Table[Customer] ),
            [Return Quantity] > 0
        )

         

        Regards,

        Pat