locked
Sum of a Column resulting from Summarize Function RRS feed

  • Question

  • I need to sum the values of column resulting from the table resulting from Summarize Funtion.

    For e.g. my Data Set 'Tab' is like this

    Type        Value

    A             10

    A             10

    A             10

    B             20

    B             20

    B             20

    C             30

    C             30

    C             30

    The result from Summarize(Tab,[Type],AVG([Value])) will be like following

    A             10

    B             20

    C             30

    And the final result required from this result set is 10+20+30 i.e. 60.

    Please help

    Thursday, December 15, 2016 4:35 PM

Answers

  • You want to use SUMX() to iterate over the results of the table returned by SUMMARIZE().

    Try this:

    Result :=
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( Tab, Tab[Type] ),
            "Avg", AVERAGE ( Tab[Value] )
        ),
        [Avg]
    )
    
    ADDCOLUMNS() isn't required, but generally gives better performance than just adding the calculated columns directly within SUMMARIZE().

    Thursday, December 15, 2016 8:12 PM
    Answerer

All replies

  • You want to use SUMX() to iterate over the results of the table returned by SUMMARIZE().

    Try this:

    Result :=
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( Tab, Tab[Type] ),
            "Avg", AVERAGE ( Tab[Value] )
        ),
        [Avg]
    )
    
    ADDCOLUMNS() isn't required, but generally gives better performance than just adding the calculated columns directly within SUMMARIZE().

    Thursday, December 15, 2016 8:12 PM
    Answerer
  • Thanks.

    It worked.

    Friday, December 16, 2016 5:41 AM