Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
filipebodas
Helper I
Helper I

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_idProductProduct_TypeAmount
1A010
1B15
1C010
2A010
2C010
3B15
4B15
4C010

 

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.

 

 

1 ACCEPTED SOLUTION

@filipebodas 

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

@filipebodas 

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)

tamerj1
Super User
Super User

@filipebodas 

How does your visual look like?

I want a to display it on a card

@filipebodas 

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

@tamerj1 perfect! 

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.