Forum Discussion
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
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.
11 Replies
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?
your comment saved my life, easy to understand and easy to fix 🙂
