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
Have a table that looks like this :
I'm looking for a measure that will only sum the cost for each PO that have the latest version number .
So for example I want to have the value for Metro I want to have the cost of 5486 .
I know that I should use the MAX() function in some way
Supplier PO VersionCost
Metro | 1239057 | 0 | 486 |
Metro | 1239057 | 0 | 4614 |
Metro | 1239057 | 2 | 486 |
Metro | 1239057 | 2 | 5000 |
Metro | 1461404 | 0 | 4300 |
OFFICE | 1554413 | 0 | 225 |
OFFICE | 1557558 | 0 | 357 |
OFFICE | 1558801 | 0 | 396 |
OFFICE | 1564938 | 0 | 238 |
OFFICE | 1614940 | 0 | 60 |
OFFICE | 1614940 | 0 | 264 |
OFFICE | 1614940 | 0 | 233,1 |
OFFICE | 1614940 | 0 | 5425 |
OFFICE | 1614940 | 0 | 874,8 |
OFFICE | 1614940 | 0 | 400 |
OFFICE | 1614940 | 0 | 54 |
OFFICE | 1614940 | 0 | 620 |
OFFICE | 1614940 | 1 | 60 |
OFFICE | 1614940 | 1 | 264 |
OFFICE | 1614940 | 1 | 250 |
OFFICE | 1614940 | 1 | 600 |
OFFICE | 1614940 | 1 | 10000 |
OFFICE | 1614940 | 1 | 400 |
OFFICE | 1614940 | 1 | 54 |
OFFICE | 1614940 | 1 | 620 |
Solved! Go to Solution.
Hi @Oleander
Create measures
Measure =
VAR lastversion =
CALCULATE (
MAX ( 'Table'[Version] ),
ALLEXCEPT (
'Table',
'Table'[Supplier],
'Table'[PO]
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Cost] ),
FILTER (
ALLEXCEPT (
'Table',
'Table'[Supplier],
'Table'[PO]
),
'Table'[Version] = lastversion
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi!
I used your DAX formula to this :
VAR Lastversion:=CALCULATE(MAX(Tabell1[Version]);ALLEXCEPT(Tabell1;Tabell1[Leverantör];Tabell1[PO]))
and
Return:=CALCULATE(SUM(Tabell1[Kost]);FILTER(ALLEXCEPT(Tabell1;Tabell1[Leverantör];Tabell1[PO]);Tabell1[Version]=[VAR Lastversion]))
Is there any faults in this code ?
Found a solution that fitted my table :
CALCULATE(SUM(DBASE[Cost]);FILTER(DBASE;DBASE[Version] =CALCULATE (MAX(DBASE[Version]);FILTER(DBASE;DBASE[Supplier]))))
😎
Hi @Oleander
Create measures
Measure =
VAR lastversion =
CALCULATE (
MAX ( 'Table'[Version] ),
ALLEXCEPT (
'Table',
'Table'[Supplier],
'Table'[PO]
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Cost] ),
FILTER (
ALLEXCEPT (
'Table',
'Table'[Supplier],
'Table'[PO]
),
'Table'[Version] = lastversion
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie !
Thanks for your reply:
I've tryed your DAX now but I get the same result when I try my own DAX formula when I in the Pivot delete the PO column :
Sista PO=SUMX(FILTER(Tabell1;Tabell1[Version] = MAX(Tabell1[Version]));Tabell1[Kost])
If I deselect the "PO" column so that I only have Supplier and Return, it will only show the latest/Highest PO sum for that supplier.
Metro will show "5486" and not "9786" ((PO 1239057) 5486 + (PO 1461404) 4300)
Kind regards
Oleander
Hi!
I used your DAX formula to this :
VAR Lastversion:=CALCULATE(MAX(Tabell1[Version]);ALLEXCEPT(Tabell1;Tabell1[Leverantör];Tabell1[PO]))
and
Return:=CALCULATE(SUM(Tabell1[Kost]);FILTER(ALLEXCEPT(Tabell1;Tabell1[Leverantör];Tabell1[PO]);Tabell1[Version]=[VAR Lastversion]))
Is there any faults in this code ?
Hi !
If I only use the one of the AllExept() formulas , it will work, not with both.
=CALCULATE(SUM(Tabell1[Kost]);FILTER(ALLEXCEPT(Tabell1;Tabell1[Leverantör]);Tabell1[Version]=[Lastversion]))
Thanks
//Göran
https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814
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.