Measure to sum everything with same id

[BIanon]

Hello Comunity,

Sample data:

ProductId	Trip	Value
1	1	9
2	1	9
3	1	9
4	1	9
5	2	9
1	2	9
5	2	9
6	2	9
7	3	9
2	3	9
3	3	9

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?

[parry2k]

@BIanon I have something similar resolved here How to override slicer selection to return all records | PeryTUS - Power BI How To - YouTube

[rsbin]

@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]

Thank you for the reply.

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

[rsbin]

@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,