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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.