cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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

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.

 

TransX Table.JPGExpected result.JPG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

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

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

 

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

 

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

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

@Dobby_Libr3 , 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])


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Resolver I
Resolver I

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

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.

 

Error.JPG

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

Highlighted
Helper II
Helper II

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

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

 

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

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors