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.

               

CustomerShipment QuantityReturn QuantityReturn %
A503570%
B10055%
C34000%
D270259.25%
Total760658.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
    TotalSales
Return Quantity =
VAR TotalReturns =
    CALCULATE (
        SUM ( 'Sales Order Detail'[Custom Quantity] ),
        'Order Type'[Order Type Transaction Group] = "Return/Dump"
    )
RETURN
    TotalReturns
Return % =
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

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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_Air's avatar
      Ritesh_Air
      Post 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 )

       

      mahoneypat 

       

      No luck.

       

      • mahoneypat's avatar
        mahoneypat
        Microsoft 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