Forum Discussion
SUMMARIZE error The expression refers to multiple columns... cannot be converted to a scalar value
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.
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
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
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
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
your comment saved my life, easy to understand and easy to fix 🙂
