Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
test =
VAR last_value =
MAX( Table[Code] )
RETURN
CALCULATE (
SUM ( Table[Outsnading Value] ),
Table[Code] = last_value
)
@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])
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
test =
VAR last_value =
MAX( Table[Code] )
RETURN
CALCULATE (
SUM ( Table[Outsnading Value] ),
Table[Code] = last_value
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |