0
votes

I'm currently trying to calculate in a measure the last amount chosen in a checkout process. This needs to be identified by customer ID. The last chosen amount can be identified by using the Action ID (indexes order of events, but does not reset by customer).

Three columns are Action ID, Amount Chosen, and Customer ID (see image URL below).

EDIT 1/29/19: The output should also exclude $0's as an output option unless the Customer ID lacks a "Confirmed". The final purpose would be to have a sum of the "Desired Output".

Example data

 Action ID     Amount Chosen     Customer ID    Desired Output
 1             $10               1              
 2             $15               1              $0
 3             $20               2              
 4             $25               2              $25
 5             $0                2              
 6             Confirmed         2              
2
What example output are you expecting? Please edit into your questionDegan
I edited the above - thank you for the adviceSebastian Hubard

2 Answers

1
votes

I would use a combination of the LOOKUPVALUE and MAXX functions, e.g.

Last Amount Chosen =
LOOKUPVALUE (
    Table1[Amount Chosen],
    Table1[Action ID], MAXX (
        KEEPFILTERS ( VALUES ( 'Table1'[Customer ID] ) ),
        CALCULATE ( MAX ( 'Table1'[Action ID] ) )
    )
)

LOOKUPVALUE will pluck the value from a single row. MAXX is finding the highest Action ID for each Customer ID.

0
votes

I have tweaked Mike Honey's answer to address the new requirements. I think this works - have only done mild vetting.

Last Amount Chosen = 
VALUE(
    LOOKUPVALUE (
        TestData[AmountChosen],
        TestData[ActionID], MAXX (
            KEEPFILTERS ( VALUES ( 'TestData'[CustomerId] ) )
            , CALCULATE ( MAX ( 'TestData'[ActionID] )
                , FILTER(TestData, TestData[AmountChosen] <> "Confirmed")
                , FILTER(TestData, TestData[AmountChosen] <> "0") 
            )
        )
    )
) *  MAXX(TestData,if(TestData[AmountChosen] = "Confirmed",1,0))

This is basically the same work with a few extra filters to keep the special cases ('Confirmed' and '0') out of the result. Then we check for 'confirmed' status and get a flag value of 0 or 1. Multiplying that flag against whatever is returned by the lookup will either zero out the result if not confirmed or pass it through unchanged if it is.

It should be noted that this measure only provides meaningful information when the information is specifically grouped at the customerID level.

enter image description here