Forum Discussion

BIanon's avatar
BIanon
Icon for Helper V rankHelper V
2 years ago

Measure to sum everything with same id

Hello Comunity,

Sample data:

ProductIdTripValue
119
219
319
419
529
129
529
629
739
239
339


On a report page there is a slicer with "ProductId", if the user chooses ProductId 2 then I need to identify Trips that has this ProductId and sum the entire Trip value.

 

For ProductId 2 this would mean Trip number 1 and 3 meaning the sum total would be 63 (Trip 1 total + Trip 2 total).

Can this be done with DAX?

Need Help

4 Replies

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    2 years ago

    BIanon ,

    Yes DAX can handle this.  I would handle like this.  Create 2 Calculated Columns:

     

    Trip_Sum = CALCULATE( SUM(Products1[Value] ),
                  ALLEXCEPT( Products1, Products1[Trip] ))

ProductID_Sum = CALCULATE( SUM(Products1[Trip_Sum]),
                        ALLEXCEPT( Products1, Products1[ProductId] ))

     

    ProductId Trip Value Trip_Sum ProductID_Sum
    1 1 9 36 72
    2 1 9 36 63
    3 1 9 36 63
    4 1 9 36 36
    5 2 9 36 72
    1 2 9 36 72
    5 2 9 36 72
    6 2 9 36 36
    7 3 9 27 27
    2 3 9 27 63
    3 3 9 27 63

    With more time, you can probably do this in one step, but this gives more visibility to your calculations.

    Hope this helps.

    Regards,

    • BIanon's avatar
      BIanon
      Icon for Helper V rankHelper V
      2 years ago

      Thank you for the reply.

      Would I be able to solve this with a measure instead of a calculated col ?

      • rsbin's avatar
        rsbin
        Icon for Community Champion rankCommunity Champion
        2 years ago

        BIanon ,

        Yes, I'm sure this can be done with a Measure.  However, variables and variable tables are not my strong suit.  Going to tag a couple of folks who I think may be able to solve this with a Measure.

        parry2k , Jihwan_Kim - hope you might lend an assist here.

        Thanks and Regards,