Summarize function error: the expression refers to multiple columns

[Guinaba]

Hi experts,

When using the expression below I am getting the error: 

Summarize function error: the expression refers to multiple columns

Any idea what I am missing?

MyMeasure:=SUMMARIZE( Range,Range[Date], "Total Value", sum( Range[Sales]))

Date	Customer	City	Country	Sales
20/01/2025	A	Sydney	Australia	15
27/01/2025	A	New York	US	20
3/02/2025	A	Melbourne	Australia	10
10/02/2025	A	Sao Paulo	Brazil	16
17/02/2025	A	London	UK	13
24/02/2025	A	Rome	Italy	13
3/03/2025	A	Paris	France	11
20/01/2025	B	Sydney	Australia	17
27/01/2025	B	New York	US	14
3/02/2025	B	Melbourne	Australia	15
10/02/2025	B	Sao Paulo	Brazil	20
17/02/2025	B	London	UK	11
24/02/2025	B	Rome	Italy	15
3/03/2025	B	Paris	France	15
20/01/2025	C	Sydney	Australia	12
27/01/2025	C	New York	US	15
3/02/2025	C	Melbourne	Australia	17
10/02/2025	C	Sao Paulo	Brazil	10
17/02/2025	C	London	UK	12
24/02/2025	C	Rome	Italy	19
3/03/2025	C	Paris	France	17
20/01/2025	D	Sydney	Australia	13
27/01/2025	D	New York	US	11
3/02/2025	D	Melbourne	Australia	12
10/02/2025	D	Sao Paulo	Brazil	14
17/02/2025	D	London	UK	15
24/02/2025	D	Rome	Italy	14
3/03/2025	D	Paris	France	16

[Irwan]

hello @Guinaba 

your DAX works perfectly if your expected result is a table.

however, looks like you put the DAX into measure which has scalar return value (not a column) the you got this error.

here is return value of SUMMARIZE() based on SUMMARIZE function (DAX) - DAX | Microsoft Learn

you can use SUMMARIZE() in measure, but i believe you need to do SUMMARIZE() in virtual table then take the value from virtual table as your measure result.

Hope this will help.

Thank you.

[Canguru]

Thanks @Irwan ,

How did you return a virtual table using the summarize expression? I have created the measure using PowerPivot. How can I create the virtual table in PowerPivot?

[Anonymous · Accepted solution]

Hi @Guinaba ,

Irwan has explained very clearly why this error is reported and given a solution. If you want to get the summarized values group by the data, you can also create a table visual in Power BI directly with below setttings to get it:

Hi @Canguru ,

You can refer the following link to insert a pivot table to get it just as shown in below screenshot:

Power Pivot Principles: Introducing the SUMMARIZE Function

Best Regards

[Irwan]

hello @Canguru 

i would do something like this.

MyMeasure = 
var _Sum = 
SUMMARIZE(
    'Range',
    'Range'[Date],
    'Range'[Sales]
)
Return
SUMX(
    _Sum,
    'Range'[Sales]
)

Virtual table stored as variable then calculate any value from that variable.

Hope this will help.

Thank you.