Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Icon for Post Patron rankPost 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
Icon for Microsoft Employee rankMicrosoft 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
Icon for Post Patron rankPost 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
    Icon for Microsoft Employee rankMicrosoft 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

    • Ritesh_Air's avatar
      Ritesh_Air
      Icon for Post Patron rankPost Patron
      6 years ago

      mahoneypat  Thanks, it is Brand. Customer was just an example I was trying to give to state the problem.

       

      Can somehow I can create a custom table and pick and choose columns from the table I posted earlier? It seems so easy but looks like needs to go through lot of hoops.

       

      thanks for your help.

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        You can make a calculated table, but you would likely run into same issue.  Basically, we are trying to make the right virtual table as part of this measure.  If you are willing to share your pbix (with mock data or send a link through private message), I can figure it out.  Someone else may have an idea too.

         

        Regards,

        Pat