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

Thanks for sending the pbix.  This measure returns the desired result of 32.17%.  Also below is a pic with one of your tables, and a new table I made with just Brand and this measure.  Note that the measure returns 100% for each row on your existing table, as each row either has returns or not (so the two variables are the same when there are returns).

 

% With Returns =
VAR __totalqty =
    SUM ( '2020 Actual Returns by Total Sales'[Shipment Quantity] )
VAR __totalwreturns =
    CALCULATE (
        SUM ( '2020 Actual Returns by Total Sales'[Shipment Quantity] ),
        '2020 Actual Returns by Total Sales'[Return Quantity] > 0
    )
RETURN
    DIVIDE ( __totalwreturns, __totalqty )

 

 

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

Thanks Pat.

mahoneypat 

 

So close but not there yet. 😞

 

 

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    The pbix you sent had a single table while the measure in your screen shot has multiple tables, so it wasn't represetative of your data model.  If you can't send the actual pbix file, please provide a screen shot of the diagram view/data model, so we can see the relationships between these tables.

     

    Regars,

    Pat

     

  • Ritesh_Air's avatar
    Ritesh_Air
    Post Patron
    6 years ago

     

    mahoneypat 

     

    Thank you. Here it is. Sorry about that. It is based off of the live dataset. 

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Not sure if there is a record for the most back and forths, but, since the returns and sales are on different rows, we need some way to filter out the sales rows that have >0 returns.  You did that in the visual with a filter on the Returns measure, and I tried to do that with the FILTER(ALLSELECTED( ) on Product Brand (to replicate what you did in the visual).  What else do the rows have in common?  Is there a parent sale ID or something that associates them?

     

    Signing off soon for the night.  I will pick it up tomorrow, if someone else doesn't give a solution first.

     

    Regards,

    Pat