cancel
Showing results 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.

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:
 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

1 ACCEPTED SOLUTION
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

Did I answer your question? Mark my post as a solution! This will help others on the forum!
3 REPLIES 3
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

Did I answer your question? Mark my post as a solution! This will help others on the forum!
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

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

Perf, works...

Life can be so easy 🙂

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors