Need Help With Dax Table Query

[Anonymous]

I have the below Dax measure that produces a list of parent suppliers with the sum of the spend on them from a fact table.

Parent Supl Spend = var parents = SUMMARIZE('Export','Export'[ParentSupplierName],'Export'[LeaderName],"Spend",SUM('Export'[Spend Amount]))

Return parents

The lines returned from one supplier look like this:

I would like to extract from this output ONLY the line with the GREATEST spend.   How would I do that?

Mark Janecek

PTP Metrics Analyst 

Johnson Controls

[Anonymous]

Thank you for the help!

The below code First returns 2 tables of supplier keys that have invoiced in one system or another.(aribainvs & fs2invs).

Then it returns a table of a union of those 2 results (_allinvs).  

I am trying to take the union and return every entry from the table named Supplier lookup that does NOT have a Supplier Key in the Union.  

My CALCULATETABLE Function is saying the _allinvs table variable can't be found.  I don't understand why?

EVALUATE
/*Retruns table of supplier keys that invoiced with Ariba*/
VAR aribainvs =
    SUMMARIZE (
        FILTER (
            'Onboarding Report',
            NOT (
                ISBLANK ( 'Onboarding Report'[Invoice Submit Date] )
            )
        ),
        'Onboarding Report'[SupplierKey]
    ) 
    
    /*Returns Table of supplier keys that Invoices with FS2*/
VAR fs2invs =
    DISTINCT ( 'FS2 Invoices'[SupplierKey] )
VAR _allinvs =
    UNION (
        aribainvs,
        fs2invs
    )
VAR noinvs =
    CALCULATETABLE (
        _allinvs,
        _allinvs[SupplierKey] <> 'Supplier Lookup'[SupplierKey]
    )
RETURN
    _allinvs

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to try this measure.

GROUP =
CALCULATE (
    MAX ( 'Parent Supl Spend'[Spend] ),
    ALLEXCEPT ( 'Parent Supl Spend', 'Parent Supl Spend'[ParentSupplierName] )
)

If you want to get max spend for each user in calcualted table, please try this code.

Parent Supl Spend =
VAR _Parent =
    SUMMARIZE (
        'Export',
        'Export'[ParentSupplierName],
        'Export'[LeaderName],
        "Spend", SUM ( 'Export'[Spend Amount] )
    )
VAR _MAX =
    SUMMARIZE (
        FILTER (
            _Parent,
            VAR _MaxSpend =
                MAXX (
                    FILTER ( _Parent, [ParentSupplierName] = EARLIER ( [ParentSupplierName] ) ),
                    [Spend]
                )
            RETURN
                [Spend] = _MaxSpend
        ),
        [ParentSupplierName],
        [LeaderName],
        [Spend]
    )
RETURN
    _MAX

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Anonymous 

please use

Parent Supl Spend =
TOPN (
    1,
    SUMMARIZE (
        'Export',
        'Export'[ParentSupplierName],
        'Export'[LeaderName],
        "Spend", SUM ( 'Export'[Spend Amount] )
    ),
    [Spend]
)