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
Tim
Regular Visitor

SUMMARIZE error The expression refers to multiple columns... cannot be converted to a scalar value

In general I am struggling with summarizing data in Report View.  I've looked for example at https://msdn.microsoft.com/en-us/library/gg492171.aspx

 

But find with this measure:  AcctTotal3 = SUMMARIZE(ChargeActivity , ChargeActivity[Date], ChargeActivity[Account Id] ,"Invoice Price", SUM(ChargeActivity[Invoice Price]) )  

 

Where simplistically I have a ChargeActivity Table as below, I want to summarize in one row the Total Invocie Price for each Date/Account ID combination.

 

I get the error in the title. I have trtied multiple variations and got different errors.  What am I not getting?

 

Date   Account ID  << other columns >>   Invoice Price

3/4      12              GBR SMS                        £10

3/4      12              FRA SMS                        £4

3/4      12              USA SMS                        £2

3/4      13              BEL SMS                        £1

3/4      13              USA SMS                        £6

 

5/4      12              GBR SMS                        £10

5/4      12              FRA SMS                        £4

5/4      13              GBR SMS                        £13

5/4      13              USA SMS                        £6

 

1 ACCEPTED SOLUTION
SqlJason
Memorable Member
Memorable Member

You seem to have 2 commas, I have highlighted and underlined that part.

calculate(sum(ChargeActivity[Invoice Price])), , "CostpRow", calculate(sum(MTpAcct[Cost/Row])))

 

 

Just change that to one comma :)... The error is basically saying that yo have specified one extra arguement or one less, because of the redundant comma.

View solution in original post

11 REPLIES 11
SqlJason
Memorable Member
Memorable Member

The problem is that Summarize returns a table and you are trying to assign it to a scalar value (a measure).

 

Where are you trying to display the result? If you just drag and drop the Total Price in your columns with Account and Date on the rows, you will automatically get the total price for each account/date combination.

 

If you want the same value to be repeated for each account/date combination even if other columns are there, then you need to do something like

calculate(SUM(ChargeActivity[Invoice Price]), ALLEXCEPT(ChargeActivity, ChargeActivity[Date], ChargeActivity[Account Id])

 

Or if you just want the sum of total price for each account/date combination regardless of what is there on rows, you will have to do something like a 

SUMX(CROSSJOIN(values(Date), values(Account)), calculate(SUM(ChargeActivity[Invoice Price])) , etc...

 

If you tell me your requirements in detail with some source data and the result, I might be able to help you better

your comment saved my life, easy to understand and easy to fix 🙂

Thanks for replying SqlJason. 

 

When you say Total Price I’m not sure what you mean, I created a measure = SUM(ChargeActivity[Invoice Price])   but this gives the same data as Invoice Price.  This just gives the data for that row not multiple rows

 

I put in your measure >> calculate(SUM(ChargeActivity[Invoice Price]), ALLEXCEPT(ChargeActivity, ChargeActivity[Date], ChargeActivity[Account Id])  … it makes every row have the same data in

 

I did not try your SUMX suggestion yet.

 

Likely stupid question - how do I write / what do I call / an expression that creates a table.. I guess it is not a measure.,..

 

I would very much appreciate telling you the requirements / data for your help.  I’m good with Excel / pivot tables but not SQL / a developer so this is a new world.  How can I best get you info..I’m ok with pulling data from different tables with RELATED so I guess I could give you just a better example version of the data already shown and an idea of what I’m trying to get in an excel file.  It will be tomorrow now

SqlJason
Memorable Member
Memorable Member

t1.png

This is what I am getting when I am using the formula. You can see that 16 is repeating 3 times, as the date is 3/4 and account is 12. (so it is adding 10+4+2)..

 

Now you might also want the aggregation to be an average, so that if you put that in a pivot table, it will only show 16 and not 16*3. If this is not your requirement, can you show how you want your final data to look like, so that I can better understand your requirements?

And you can create a new table also if you want in Power BI. Go to the modelling section of PBI, and you can see Data Tools->Modelling->New Table. You can write the expression below to create the table

 

TableName=summarize(Table, Table[AccountID], Table[Date], "InvPrice", calculate(sum(Table[InvoicePrice])))

 

This should give you the new table which has the sum of invoice proce by Account and Date

Thanks for this again SqlJason

 

The ability ot create a new table from an expression has unlocked a lot of possiblities.  Long term I want to understand enough to do whatever I want  but short term for this thread we are nearly there, using the new New table

 

A NewTable = summarize(ChargeActivity, ChargeActivity[Acct2Use], ChargeActivity[Date],ChargeActivity[Account Name], "Invoice Price", calculate(sum(ChargeActivity[Invoice Price])), , "CostpRow", calculate(sum(MTpAcct[Cost/Row])))

 

or VERY similar was working great but I wanted to add aother column for Account Id (having changed ot using Acct2Use.. long story) .. I wanted this as a reference information only without grouping by it so I tried to put it in as another argument like ..."CostpRow", calculate(sum(MTpAcct[Cost/Row]))... ..but without summing it.  This did not work and now in going back to what did work I get the error..Argument "7" in SUMMARIZE function is required...  Can you see what in the above would cause that?

 

If you can sort this I'll accept this as a response and re-post the next question!

 

 

-

 

- It does not seem to 

SqlJason
Memorable Member
Memorable Member

You seem to have 2 commas, I have highlighted and underlined that part.

calculate(sum(ChargeActivity[Invoice Price])), , "CostpRow", calculate(sum(MTpAcct[Cost/Row])))

 

 

Just change that to one comma :)... The error is basically saying that yo have specified one extra arguement or one less, because of the redundant comma.

Anonymous
Not applicable

=SUMMARIZE(Table3,Table3[Group],"HC COMMIT LM",CALCULATE(AVERAGE(Table3[HC CAMPAIGN LM])))

 

Hi all, Please help to look out my sentence as above and give me advice for correcting it.

embarrassing!.. I'll be back I'm sure.  many thanks

Thanks for this again SqlJason

 

The ability ot create a new table from an expression has unlocked a lot of possiblities.  Long term I want to understand enough to do whatever I want  but short term for this thread we are nearly there, using the new New table

 

A NewTable = summarize(ChargeActivity, ChargeActivity[Acct2Use], ChargeActivity[Date],ChargeActivity[Account Name], "Invoice Price", calculate(sum(ChargeActivity[Invoice Price])), , "CostpRow", calculate(sum(MTpAcct[Cost/Row])))

 

or VERY similar was working great but

-

 

- It does not seem to 

Thanks for replying SqlJason. 

 

When you say Total Price I’m not sure what you mean, I created a measure = SUM(ChargeActivity[Invoice Price])   but this gives the same data as Invoice Price.  This just gives the data for that row not multiple rows

 

I put in your measure >> calculate(SUM(ChargeActivity[Invoice Price]), ALLEXCEPT(ChargeActivity, ChargeActivity[Date], ChargeActivity[Account Id])  … it makes every row have the same data in

 

I did not try your SUMX suggestion yet.

 

Likely stupid q

 

I would very much appreciate telling you the requirements / data for your help.  I’m good with Excel / pivot tables but not SQL / a developer so this is a new world.  How can I best get you info..I’m ok with pulling data from different tables with RELATED so I guess I could give you just a better example version of the data already shown and an idea of what I’m trying to get in an excel file.  It will be tomorrow now

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.

Top Solution Authors