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

[Tim]

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

[SqlJason]

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

[thaomyltr]

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

[Tim]

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]

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?

[SqlJason]

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

[Tim]

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 · Accepted solution]

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]

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

[Tim]

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

[Tim]

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 

[Tim]

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