March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to Solution.
@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)
@Anonymous
How does your visual look like?
I want a to display it on a card
@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] ) ) )
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
23 | |
15 | |
12 | |
9 | |
8 |
User | Count |
---|---|
41 | |
32 | |
29 | |
12 | |
12 |