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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors