Measure to calculate with filters MAX()

[Oleander]

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

[v-juanli-msft · Accepted 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.

[Oleander]

Found a solution that fitted my table :

CALCULATE(SUM(DBASE[Cost]);FILTER(DBASE;DBASE[Version] =CALCULATE (MAX(DBASE[Version]);FILTER(DBASE;DBASE[Supplier]))))

😎

[v-juanli-msft · Accepted 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.

[Oleander]

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

[Oleander · Accepted solution]

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 ?

[Oleander]

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

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814