Return a value from a column based on MAX from another column

[Anonymous]

Hi Commuinty,

So I have table with a list of transactions, in this senario where I want to return the [Outstanding Value] based on the MAX [code] thats relevant to the Doc number.

The Items in red have been filtered out using a page level filter, and the expected result is in a matrix with the DocNumber being the lowest level. This also has to be in a measure because to make the expected result table I have used a summarize  version of the data table.

any help is appricated, 

Dobby Libr3

[amitchandak]

@Anonymous , try one of the two

LASTNONBLANKVALUE(Table[Code],sum(table[outstanding Value]))

sumx(summarize(Table, Table[Doc Number],"_1",LASTNONBLANKVALUE(Table[Code],sum(table[outstanding Value]))),[_1])

[Anonymous]

Hi @amitchandak,

Unfortuantly it didn't work, in the matrix table it returned a 0 for the first one and sum the whole table and gave the result below in the 2nd . in the screen shot I'm expecting the measure to show as £389.62 in the bottom table.

This is the Summarize code for the bottom table - is there anyway i could nest the formaul into this? 

PIT_Debt = SUMMARIZE('dwh PaymentStatusHistory','dwh PaymentStatusHistory'[DocumentId],
"DocNumber", MAX('dwh PaymentStatusHistory'[DocumentNumber]),
"Status Code", MAX('dwh PaymentStatusHistory'[StatusCode]),
"Status", MAX('dwh PaymentStatusHistory'[Status]),
"Date", MAX('dwh PaymentStatusHistory'[DocumentStatusHistoryDate]),
"Company", MAX('dwh PaymentStatusHistory'[Company]),
"Dimension 1", MAX('dwh PaymentStatusHistory'[Dimension1]),
"Account", MAX('dwh PaymentStatusHistory'[Account]),
"OutstandingValue", MIN('dwh PaymentStatusHistory'[Value]) // This needs to show the £389.62
)

Thanks for looking

[omillzy · Accepted solution]

Hi @Anonymous and @amitchandak would the following measure work?

test = 
VAR last_value =
    MAX( Table[Code] )
RETURN
    CALCULATE (
        SUM ( Table[Outsnading Value] ),
        Table[Code] = last_value
    )