Forum Discussion

Tim's avatar
Tim
Regular Visitor
10 years ago
Solved

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 = SUMMA...
  • SqlJason's avatar
    SqlJason
    10 years ago

    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.

SqlJason's avatar
SqlJason
Memorable Member
10 years ago

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

Tim's avatar
Tim
Regular Visitor
10 years ago

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