Forum Discussion
Select distinct rows for SUMX for related table
I have two tables.
Releases
ReleaseID |
A |
| B |
| C |
Tickets
| TicketID | ReleaseID | MyValue | Stuff |
| 1 | A | 2 | xyz1 |
| 1 | A | 2 | abc1 |
| 2 | B | 5 | xyz2 |
| 2 | B | 5 | abc2 |
| 3 | B | 1 | xyz3 |
| 4 | C | 10 | xyz4 |
Both tables are related in the data modell via the ReleaseID fields, i.e. the relation is 1:m from table Releases to table Tickets. I am looking for a way to get the sum of MyValue per ReleaseID for distinct values of TicketID. The value of MyValue is guaranteed to be identical for all rows with the same TicketID, those rows only differ by the entries in Stuff. But I want to count each TicketID only once in my sum.
So the result should be:
ReleaseID | SumMyValue |
A | 2 |
| B | 6 |
| C | 10 |
I guess I would somehow have to combine a SUMX over the RELATEDTABLE plus filtering on distinct values. But I can't get it to work.
Right now all my attempts lead to summing all MyValue values for the rows in Tickets, thus yielding A -> 4, B -> 11, C -> 10.
Anonymous try this measure, Table 2 in my measure is table from many side and Table is from One side
Total Value = SUMX ( SUMMARIZE ( 'Table 2', 'Table'[ReleaseID], 'Table 2'[TicketID] ), CALCULATE ( MAX( 'Table 2'[MyValue] ) ) )Would appreciate Kudos 🙂 if my solution helped.
3 Replies
- parry2k
Super User
Anonymous try this measure, Table 2 in my measure is table from many side and Table is from One side
Total Value = SUMX ( SUMMARIZE ( 'Table 2', 'Table'[ReleaseID], 'Table 2'[TicketID] ), CALCULATE ( MAX( 'Table 2'[MyValue] ) ) )Would appreciate Kudos 🙂 if my solution helped.
parry2k thanks for your suggestion.
It works - but I can't yet really figure out why and how. Putting your formula in a measure and then creating a calculated column that uses this measure gives the correct results per row in my "one-side" table.
Putting your formula directly into the calculated column however gives incorrect results.
Could you maybe elaborate a bit more on how SUMX does it's magic here?
- parry2k
Anonymous summarize is making the unique row and taking the maximum value of it and then sumx is summing it up. Hope this helps.