Conditionally change row values based on previous row

it is feasible when create separate measures for each ticket type.

hope it helps.

measures to create:

1. Total Qty = SUM(Sheet1[Qty])

2. Free = CALCULATE([Total Qty],Sheet1[Ticket_Type]="Free")

3. Free Upgrade = CALCULATE([Total Qty],Sheet1[Ticket_Type]="Free Upgrade")

4. Paid Qty = CALCULATE([Total Qty],Sheet1[Ticket_Type]="Paid")
5. Paid Upgrade = CALCULATE([Total Qty],Sheet1[Ticket_Type]="Paid Upgrade")

6. Premium = CALCULATE([Total Qty],Sheet1[Ticket_Type]="Premium")

Hi ChrisHill ,

According to your description, here's my solution.

1. First, we should create a new table, as in your calculation, the ticket type "Premium" belongs to "Paid", but some customer hasn't the type "Paid", for example "Bridget".

Create a new table, don't make relationship between the two tables.

Table 2 = GENERATE(VALUES('Table'[Customer]),VALUES('Table'[Ticket_Type]))

2. Create a measure.

Measure =
VAR _Q =
    SWITCH (
        MAX ( 'Table 2'[Ticket_Type] ),
        "Free Upgrade",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Customer] = MAX ( 'Table 2'[Customer] )
                        && 'Table'[Ticket_Type] = "Free Upgrade"
                ),
                'Table'[Qty]
            ),
        "Free",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Customer] = MAX ( 'Table 2'[Customer] )
                        && 'Table'[Ticket_Type] = "Free"
                ),
                'Table'[Qty]
            )
                - SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Customer] = MAX ( 'Table 2'[Customer] )
                            && 'Table'[Ticket_Type] IN { "Free Upgrade", "Free Refund" }
                    ),
                    'Table'[Qty]
                ),
        "Paid Upgrade",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Customer] = MAX ( 'Table 2'[Customer] )
                        && 'Table'[Ticket_Type] = "Paid Upgrade"
                ),
                'Table'[Qty]
            ),
        "Paid",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Customer] = MAX ( 'Table 2'[Customer] )
                        && 'Table'[Ticket_Type] IN { "Paid", "Premium" }
                ),
                'Table'[Qty]
            )
                - SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Customer] = MAX ( 'Table 2'[Customer] )
                            && 'Table'[Ticket_Type] = "Paid Upgrade"
                    ),
                    'Table'[Qty]
                )
    )
RETURN
    IF ( _Q <= 0, BLANK (), _Q )

Put the columns in the new table and the measure in a matrix, get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.