Finding latest value using Sequence ID and grouped by Customer ID

[Anonymous]

We currently have a form where people can select a desired amount. I would like to record this amount, but people can change their answer on the form before it is submitted. This means that every amount selection is recorded, but I need to find the last one chosen. I also have to group/reset the last amount for every customer. Additionally, I have to only record those who have completed the task (indicated with a confirmation number at the end of the session). Below is what the table would look like:

Sequence ID	Amount Chosen	Customer ID	Desired Output	Row Notes
1	100	1	0	0 because it's not most recent choice for the customer
2	25	1	0	0 because it did not complete an order indicated by the confirmation number (always "123456789")
3	0	1	0	0 because all zeros are never recorded as anything but 0
4	20	2	0	0 because it's not most recent choice for the customer
5	0	2	0	0 because all zeros are never recorded as anything but 0
6	200	2	200	Recorded because the customer ID has a confirmation number and it's the last amount chosen indicated by Sequence ID
7	123456789	2	0	0 because all confirmation numbers are never recorded as anything but 0
8	50	3	50	Recorded because the customer ID has a confirmation number and it's the last amount chosen indicated by Sequence ID
9	123456789	3	0	0 because all confirmation numbers are never recorded as anything but 0

This is the formula I was trying to use in the new column, but I still can't figure out how to keep it all grouped by Customer ID:

Column =
//returns a text as a number
VALUE(
//Returns the "Amount Chosen" for the "Sequence ID" with the value indicated below in the Maxx function
LOOKUPVALUE (
//Result Column
'Table'[Amount Chosen],
//Search Column
'Table'[Sequency ID],
//Search Value
//Returns the max value in a table (I need it be "Customer ID")
MAXX (
//Makes "Customer ID" a value regardless of filters in the calculate table
//Does not group/constrain the max to the "Customer ID" level
KEEPFILTERS ( VALUES ( 'Table'[Customer ID] ) )
//Finds max "Sequence ID" that doesn't have confirmation number (123456789) or 0
, CALCULATE ( MAX ( 'Table'[Sequence ID] )
, FILTER('Table', 'Table'[Amount Chosen] <> 123456789)
, FILTER('Table', 'Table'[Amount Chosen] <> 0)
))))

//Multiplies the max amount that isn't a 0 or confirmation number by 1 and everything else by 0
* MAXX('Table',if('Table'[Amount Chosen] = 123456789,1,0))

The end goal would be to be able to sum them all without having to break it down by Customer ID in the visual (ie: I don't want to have a separate line per Customer ID in a table)

Any help would be greatly appreciated.

Thank you. 

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the DAX below.

Column =
IF (
    RANKX (
        FILTER (
            'Table',
            'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
                && 'Table'[Amount Chosen] <> 123456789
        ),
        'Table'[Sequence ID],
        ,
        DESC
    )
        = 1
        && 'Table'[Amount Chosen] <> 123456789,
    'Table'[Amount Chosen],
    0
)