none
Error in Summarise function

    Question

  • Hi,

    I have a table named feedback with two columns - Date and Organised By.  The Organised by column has Client names.

    I want to generate a table where I Group by Organised by column and count the unique dates

    I wrote the following calculated field formula (name is Sessions conducted by client) to compute Sessions conducted per client

    =SUMMARIZE(Feedback,Feedback[Organised by],"Count of sessions",DISTINCTCOUNT(Feedback[Date]))

    When I click on Check formula, I get the following error

    Calculation error in measure 'Feedback'[Sessions conducted by client]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

    Could you please correct my formula above.

    Once the error is resolve, I will pass it to a function which will return require a Table input.

    Pleas help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, March 08, 2013 3:24 AM

Answers

  • Hi,

    Thank you for clarifying.  I used the following calculated field formula to compute the sessions conducted at Top 10 clients

    =sumx(TOPN(10,SUMMARIZE(Feedback,Feedback[Organised by],"Count of sessions",[Sessions conducted]),[Sessions conducted],0),[Sessions conducted])

    Sessions conducted is a calculated field computed as follows

    =DISTINCTCOUNT(Feedback[Date])

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com


    Friday, March 08, 2013 10:27 AM

All replies

  • Hi Ashish,

    There's nothing wrong with the syntax of the Summarize() function as you've used it here - the problem is that it returns a table of values, and a calculated column expression needs to return a single value. You can't 'see' a table of values in a single cell.

    Regards,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Friday, March 08, 2013 9:32 AM
  • Hi,

    Thank you for clarifying.  I used the following calculated field formula to compute the sessions conducted at Top 10 clients

    =sumx(TOPN(10,SUMMARIZE(Feedback,Feedback[Organised by],"Count of sessions",[Sessions conducted]),[Sessions conducted],0),[Sessions conducted])

    Sessions conducted is a calculated field computed as follows

    =DISTINCTCOUNT(Feedback[Date])

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com


    Friday, March 08, 2013 10:27 AM