locked
How to add Total Column in Power Pivot Report RRS feed

  • Question

  • Hi Al,

    Here again i come with some power pivot stuffs, i need some kind of help in customisation of Report created with the help of Power Pivot Excel feature.

    My Scenario is like :

    I have created one report as below

    1. On Row Level, i have id.

    2. On Values , i have put some measure say A

    I am having data like :

    ID       MeasureA

    1          10

    2           12

    3           45

    That is fine, i want to add total column under Measure A like below

    ID       MeasureA

    1          10

    2           12

    3           45

    Total       77

    Please suggest how to do the same.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Friday, March 22, 2013 11:17 AM

Answers

  • In Excel, this is a pivot table setting.  The default is to show subtotal and overall total:

    You can deactivate the subtotals on row/column groups by right-clicking on one of the items in the row/column level for which you want to hide the subtotal and unchecking the box shown below:

    And you can control the visibility of the overall row/column group totals via pivot-table options by right clicking anywhere in the pivot table and selecting PivotTable Options...

    BTW, if it's SSRS you have a few options.  One option is to calculate the grand-total in the tablix.  Another option is to custom-craft the MDX query instead of using the MDX-designer and explicitly requesting the all level for whatever row set(s) you're reporting on.


    BI Developer and lover of data (Blog | Twitter)


    Tuesday, March 26, 2013 4:40 PM

All replies

  • how are you creating this report...are you writing a DAX query for something like SSRS, or are you simply creating a pivot table in excel and dragging items on rows/columns/etc?

    BI Developer and lover of data (Blog | Twitter)

    Friday, March 22, 2013 6:37 PM
  • Hi Bill,

    Simply dragging measures (Computed ) into the report.

    Please help!!!!!!!!!!!!


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Monday, March 25, 2013 6:56 AM
  • Hi amit , Once you create pivot table fromyour powerpivot data  and select these coulumn , ID and measure A . it should aumatically add total in below . is it not adding ?
    Tuesday, March 26, 2013 5:49 AM
  • Hi Preeti,

    It got added, but on group based data. for e.g. when i dragID,ID1 and Measure on Pivot Table, data will be shown as below

    ID     ID2   Measure

     1      2          7

     1      2          8

    Total ID      15

     2      3          2

     2    13          6

    Total ID       8

    But actual requirement is something different what it comes, i am in need of data that will give me Total of all the ID instead segregatting into data grouping as below;

    ID     ID2   Measure

     1        2         7

     1        2         8

     2        3         2

     2      13         6

    Total ID      23

    I want Total to be imposed on all the data instead on some grouped data as shown above.

    Please let me know, if there is any work around to fetch the data as desired in above format.


    Tuesday, March 26, 2013 6:08 AM
  • In Excel, this is a pivot table setting.  The default is to show subtotal and overall total:

    You can deactivate the subtotals on row/column groups by right-clicking on one of the items in the row/column level for which you want to hide the subtotal and unchecking the box shown below:

    And you can control the visibility of the overall row/column group totals via pivot-table options by right clicking anywhere in the pivot table and selecting PivotTable Options...

    BTW, if it's SSRS you have a few options.  One option is to calculate the grand-total in the tablix.  Another option is to custom-craft the MDX query instead of using the MDX-designer and explicitly requesting the all level for whatever row set(s) you're reporting on.


    BI Developer and lover of data (Blog | Twitter)


    Tuesday, March 26, 2013 4:40 PM