How do I use SUMMARIZE as a VAR?

[t_guet01]

Hey guys,

I am currently trying to create a calculated column as in my customer master data returning a string based on the highest revenue from the sales data. Say, customer A buys from sales manager X for 10 € and from sales manager Y 20 €, I would like to return "sales manager Y". I tried doing this with SUMMARIZE in a DAX measure, because I do not want to create a slave table.

I tried the following:

MyCalcColumn=

VAR PrimaryManager =

    Summarize('Sales Lines','Sales Lines'[Customer],'Sales Lines'[Sales manager],"Net",[Net])

Return

Maxx(

    TOPN(1,PrimaryWarehouse,[Net],DESC),
    'Sales Lines'[Sales manager])

However, it keeps returning the most "grossing" sales managers over all customers for every customer. What am I doing wrong?

Sample data:

 Sales Lines:

Customer	Sales manager	Net
A	X	10
A	Y	20
B	Z	30

Customer data:

Customer

A

B

Desired customer data incl. calculated column:

Customer	MyCalcColumn
A	Y
B	Z

 Thanks and best

T

[bcdobbs]

You might want to try solving the problem as follows below which would also deal with cases where there is a tie.

MyCalcColumn = 
    //Find highest net value related to customer. Note that RELATEDTABLE automatically forces a context transition.
    VAR MaxNet = MAXX ( RELATEDTABLE ( 'Sales Lines' ), 'Sales Lines'[Net] )

    //Retrieve a table of all sales managers related to customer who achieved this highest net value.
    VAR tblBestSalesManagers = 
        CALCULATETABLE (
            VALUES ( 'Sales Lines'[Sales manager] ),
            'Sales Lines'[Net] = MaxNet
        )

    //Concatenate rows of the table together to deal with cases where there is a tie.
    VAR BestSalesManager =
        CONCATENATEX ( tblBestSalesManagers, [Sales manager], ", " )

    RETURN BestSalesManager

[bcdobbs · Accepted solution]

Not by a computer however... I think the issue is you're not forcing a context transition. Eg it's not taking account of the current row.

As a starting point wrap your summarise in CALCULATETABLE ( Your summarise statement )

[t_guet01]

Perf, works...

Life can be so easy 🙂