Conditionally change row values based on previous row

[ChrisHill]

I need to provide ticket type counts for each person and our CRM counts upgrades as a quantity even if a ticket was issued previously. If I choose a free ticket at checkout and later upgrade that ticket, the CRM shows two tickets issued. If I choose a free ticket and select an upgrade in the same transaction it is one ticket. When I have an upgrade transaction I need to check previous transactions and if there has been a free ticket issued, don't count the free ticket, just count the upgrade. If one upgrade is purchased and two free tickets had previously been issued, the count would be one free and one upgrade. The same needs to happen with "Paid" tickets and upgrades to "Paid". The difference between free and paid is club members get a maximum of 2 free tickets and non-members cannot get free tickets. Let me provide some data and then what my end report should look like.

This is what our CRM provides

Transaction	Customer	Ticket_Type	Qty	Paid
1	Sally	Free	2	0
2	Sally	Free Upgrade	1	25
3	John	Free Upgrade	2	50
4	Richard	Paid	1	35
5	Willis	Premium	1	75
6	Willis	Free	1	0
7	Daniel	Paid	2	70
8	Daniel	Paid Upgrade	1	25
9	Bridget	Free	2	0
10	Bridget	Free Refund	1	0
11	Bridget	Premium	2	150
12	Bob	Free	1	0
13	Bob	Free	1	0
14	Bob	Free Upgrade	1	25
15	Bob	Free Upgrade	1	25
16	Sally	Paid	1	35

This is what I need to provide

[VIJAYKUMART · Accepted solution]

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

[v-yanjiang-msft · Accepted solution]

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.

[ChrisHill]

I like this solution and it will actually help me out with another similar issue - just a little more complex. I'll be adding in guests to purchasers.

[VIJAYKUMART · Accepted solution]

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