Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
t_guet01
Helper I
Helper I

How do I use SUMMARIZE as a VAR?

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:
CustomerSales managerNet
AX10
AY20
BZ30

 

Customer data:

Customer
A
B

 

Desired customer data incl. calculated column:

CustomerMyCalcColumn
AY
BZ

 

 Thanks and best

T

1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

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 )



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

3 REPLIES 3
bcdobbs
Super User
Super User

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


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
bcdobbs
Super User
Super User

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 )



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Perf, works...

Life can be so easy 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.