Forum Discussion
Conditional Grouped SUM
Hello everyone,
I am having trouble to sum a column by ticket ID but the ticket_ID must follow a rule.
| Ticket_id | Product | Product_Type | Amount |
| 1 | A | 0 | 10 |
| 1 | B | 1 | 5 |
| 1 | C | 0 | 10 |
| 2 | A | 0 | 10 |
| 2 | C | 0 | 10 |
| 3 | B | 1 | 5 |
| 4 | B | 1 | 5 |
| 4 | C | 0 | 10 |
I want to sum the total sales, of tickets that have at least a Product_type = 1.
In the example above, the result should sum the total amount of tickets 1, 3 and 4 (a total of 45) because they have at least one product of type 1.
Anonymous
Try
Product Total Amount = SUMX ( VALUES ( Data[Ticket_id] ), VAR Check = CALCULATE ( COUNTROWS ( CALCULATETABLE ( Data, Data[Product_Type] = 1 ) ) ) RETURN IF ( NOT ISBLANK ( Check ), CALCULATE ( SUM ( Data[Amount] ) ) ) )
5 Replies
Anonymous
How does your visual look like?
Anonymous
Try
Product Total Amount = SUMX ( VALUES ( Data[Ticket_id] ), VAR Check = CALCULATE ( COUNTROWS ( CALCULATETABLE ( Data, Data[Product_Type] = 1 ) ) ) RETURN IF ( NOT ISBLANK ( Check ), CALCULATE ( SUM ( Data[Amount] ) ) ) )
Anonymous
The iteration ove the values of ID's will provide access to aggregation over ID level as will as to check conditions row by row (aggregated at ID level)