+ Reply to Thread
Results 1 to 13 of 13

Power Pivot Row Totals

  1. #1
    Registered User
    Join Date
    02-05-2020
    Location
    Victoria, BC Canada
    MS-Off Ver
    365 Business
    Posts
    14

    Power Pivot Row Totals

    Hi!
    I am trying to create a daily bank deposit from 3 different departments. The daily cash totals to be deposited are the sum of the clerks for each day in each department.
    I have created a power pivot to grab the daily totals by department, but I can't get the rows to total.
    It sounds like as soon as you're in this sandbox that the normal pivot table options (Show grand totals for rows and add a calculated field) are unavailable.
    Does anyone know how I could achieve this? Or even better, have all the department totals aggregated by day?
    Although given my lack of skillz whichever is easier is likely preferred.
    Sample attached.
    Thanks in advance for any help!
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Power Pivot Row Totals

    If I were you I'd use Power Query/Get and Transform. To keep it simple, I'd load all the data into queries, append them into one, then create a pivot table from that. See attached.
    Attached Files Attached Files
    Rory

  3. #3
    Registered User
    Join Date
    01-02-2020
    Location
    Munich, Germany
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Power Pivot Row Totals

    Hi,


    the following Measure can solve your Problem. UNION works only, if all columns of the tables are the equal.

    SumRows:=SUMX(UNION(Table2,Table3,Table4),[Cash])


    Greetings


    Christian
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-05-2020
    Location
    Victoria, BC Canada
    MS-Off Ver
    365 Business
    Posts
    14

    Re: Power Pivot Row Totals

    Hi Rory,
    I suspected that this was the correct solution, but I was hoping not to have to learn a whole new discipline just to crack one problem. I suspect I'll have to get up to speed on this eventually!
    Thanks!

  5. #5
    Registered User
    Join Date
    02-05-2020
    Location
    Victoria, BC Canada
    MS-Off Ver
    365 Business
    Posts
    14

    Re: Power Pivot Row Totals

    Christian,
    This will totally work for what I'm trying to do. It doesn't create a data wonderland like the get and transform solution does, but it concisely cracks my specific problem.
    Thank you for this!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Pivot Row Totals

    I'd recommend avoiding SUMX in this case, as it's iterator function and will need to be evaluated for each row.

    Instead, you can simply use SUM() measures and add. SUM is aggregation function and will evaluate for each context (i.e. row labels, rather than each row of table(s)).

    Ex:
    =SUM(Table2[Cash])+SUM(Table3[Cash])+SUM(Table4[Cash])

    EDIT: However, I'd strongly recommend following Rory's suggestion of using PowerQuery/Get & Transform. In almost all cases, data should be normalized for reporting need before loading it to data model.
    Meaning data should conform to star schema (1 fact table with multiple dimensions), or fact constellation schema (collection of many star schema).
    This will optimize DAX performance and will ensure any source table change does not break data model.
    Last edited by CK76; 02-06-2020 at 02:23 PM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  7. #7
    Registered User
    Join Date
    02-05-2020
    Location
    Victoria, BC Canada
    MS-Off Ver
    365 Business
    Posts
    14

    Re: Power Pivot Row Totals

    ya, and I already tripped over a problem with UNION which requires uniformity in the tables. the reason I'm using 3 different ones is that the source data is configured differently so the tables, while they contain the common element of 'cash' they arrive at the cash total very differently.

    I guess I'm going to have a go at learning get and transform. *sigh*

    Thanks all!

  8. #8
    Registered User
    Join Date
    02-05-2020
    Location
    Victoria, BC Canada
    MS-Off Ver
    365 Business
    Posts
    14

    Re: Power Pivot Row Totals

    OK, now my 'append' sheet is picking up the table TOTALS as well as the data... does anyone know how to stop that?

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Pivot Row Totals

    You can just use filter on the column (i.e. Filter out if row value contains "TOTAL").

  10. #10
    Registered User
    Join Date
    02-05-2020
    Location
    Victoria, BC Canada
    MS-Off Ver
    365 Business
    Posts
    14

    Re: Power Pivot Row Totals

    Copy that!
    Thanks!
    Hey do you folks find this Get and Transform thing to be super-fragile? I've had to rebuild it a dozen times... anything you do in the tables, add a column, it breaks, change a heading, and the append breaks... I was hoping to have normal humans filling in the data in the tables, but it seems like any kind of fat fingering is going to break the query.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Pivot Row Totals

    Get & Transform is very stable and robust (well, not the one they shipped as add-on for Excel 2010, but that's very long time ago).

    It's developed by SQL team and requires little thinking adjustment if you are more used to working in Excel space.

    M-Query language is more strict, in that code syntax is case sensitive, along with very strong data type validation (ex: you can't perform concatenation on numeric; use comparison operator on mismatched data types etc).

    But once you get used to it, you'll find that it is the best feature added to Excel (along with PowerPivot). It allows data to flow from source, to ETL to data model seamlessly.

  12. #12
    Registered User
    Join Date
    02-05-2020
    Location
    Victoria, BC Canada
    MS-Off Ver
    365 Business
    Posts
    14

    Re: Power Pivot Row Totals

    I'm sure it's internally robust, but it feels very exposed when you have users doing data entry into the tables who are quite capable of typing a number into the header label and saying oops! and carrying on. This will destroy the append linkages. It's not the software itself I'm worried about!

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Pivot Row Totals

    Creating small Userform for data entry goes a long way in ensuring data structure conforms to set up

    Though I tend to use db as backend rather than Excel sheets.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  2. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 04:09 PM
  3. Power Query and Power Pivot Table
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 06:17 AM
  4. Power Query and Power Pivot Table !!
    By haitham.shop in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2018, 06:15 AM
  5. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-06-2018, 05:59 AM
  6. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  7. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1