locked
Sum row in powerpivot RRS feed

  • Question

  • How can sum row not column in power pivot like worksheet in excel
    Monday, May 8, 2017 5:46 PM

Answers

  • How can sum row not column in power pivot like worksheet in excel

    So I'm guessing that you want to sum the values going horizontally across the data grid instead of down.

    You can't do this with a SUM( <range> ) style expression, you'd have to create a calculated column with an expression like [<column 1>] + [<column 2>] + ... + [<column n>]

    But a better approach is to pivot your data so that all the values you need to sum are in the one column.

    So if you have a table like: 

    Jan Feb Mar Apr ....
    11  22   33   44

    It will become:

    Month   Value
    Jan        11
    Feb        22
    Mar        33
    Apr         44
    ...


    http://darren.gosbell.com - please mark correct answers

    Tuesday, May 9, 2017 8:01 AM

All replies

  • Hi Monhamed Helmay,

    You can add a index column, then sum the rows you want by filtering index column. 

    For example, I create the following sample table, and add index column to recognize every row.



    You can create a measure if you want to sum value from second row to third row.

    sum from second to third rows:=CALCULATE(SUM(Table7[Value]),FILTER(Table7,Table7[index]>=2&&Table7[index]<=Table7[index]))

    Please feel free if you have other issue.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 9, 2017 1:40 AM
  • How can sum row not column in power pivot like worksheet in excel

    So I'm guessing that you want to sum the values going horizontally across the data grid instead of down.

    You can't do this with a SUM( <range> ) style expression, you'd have to create a calculated column with an expression like [<column 1>] + [<column 2>] + ... + [<column n>]

    But a better approach is to pivot your data so that all the values you need to sum are in the one column.

    So if you have a table like: 

    Jan Feb Mar Apr ....
    11  22   33   44

    It will become:

    Month   Value
    Jan        11
    Feb        22
    Mar        33
    Apr         44
    ...


    http://darren.gosbell.com - please mark correct answers

    Tuesday, May 9, 2017 8:01 AM
  • Add the column measures together.  

    Total of multiple columns measure := [Measure 1] + [Measure 2] + [Measure 3]...

    Tuesday, May 16, 2017 8:55 AM