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

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

 

  • 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