topic Re: Conditional Grouped SUM in DAX Commands and Tips

Conditional Grouped SUM

Anonymous — Tue, 12 Apr 2022 13:57:28 GMT

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.

Re: Conditional Grouped SUM

tamerj1 — Tue, 12 Apr 2022 14:22:48 GMT

Anonymous

How does your visual look like?

Re: Conditional Grouped SUM

Anonymous — Tue, 12 Apr 2022 14:23:57 GMT

I want a to display it on a card

Re: Conditional Grouped SUM

tamerj1 — Tue, 12 Apr 2022 14:35:35 GMT

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

Re: Conditional Grouped SUM

Anonymous — Tue, 12 Apr 2022 14:43:41 GMT

tamerj1 perfect!

Is it to much to ask the rational behind your code?

Re: Conditional Grouped SUM

tamerj1 — Tue, 12 Apr 2022 15:04:05 GMT

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)