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

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

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