+ Reply to Thread
Results 1 to 8 of 8

Is it possible to put value/calculated fields between Row field in a flat pivot table?

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    Canada
    MS-Off Ver
    Office365 32-bit
    Posts
    16

    Is it possible to put value/calculated fields between Row field in a flat pivot table?

    My boss needs a sales table/report where the data is split into sections (vertically) by month and product. Each month has a series of products sold to a series of clients so the sections will have columns across to calculate total sales for each client. This means that the section for each client will have a quantity column, price column, and a calculated total sales column. The data is loaded from a power query that connects to our SQL server. The sections should also have subtotals for each month, so I figured a flat pivot table might do the trick here.

    So far, so good.

    The problem is that the calculated (sum/sigma) fields are always placed at the far right of the pivot and there doesn't seem to be an obvious way to "mix them up" with the other columns/data (ie row fields). I tried to fix this by setting the quantity sold and price columns as calculated value fields, but then they have their own sub-total (which I don't see how to turn off), so the whole thing looks ridiculous.

    Is this possible with a pivot table or am I barking up the wrong tree?

    Thanks
    Alex

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,906

    Re: Is it possible to put value/calculated fields between Row field in a flat pivot table?

    It's probably possible with Pivot Table, but not with traditional Pivot Table.

    You'll need to use Data model based Pivot Table (PowerPivot) and apply DAX measure(s) to achieve your desired result. Note that using data model, calculated fields will be disabled and all fields should be calculated via DAX (base calculation like SUM, COUNT etc can be auto generated).

    If you need further help, I'd recommend uploading sample workbook with raw data. Along with manual mock-up of desired result (need not be pivot table).

    To upload:
    Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    10-01-2012
    Location
    Canada
    MS-Off Ver
    Office365 32-bit
    Posts
    16

    Re: Is it possible to put value/calculated fields between Row field in a flat pivot table?

    Oops! I feel silly now was so focused on the traditional pivot table that I completely forgot about power pivot.
    Thank you!

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

    Re: Is it possible to put value/calculated fields between Row field in a flat pivot table?

    You are welcome

  5. #5
    Registered User
    Join Date
    10-01-2012
    Location
    Canada
    MS-Off Ver
    Office365 32-bit
    Posts
    16

    Re: Is it possible to put value/calculated fields between Row field in a flat pivot table?

    Actually, still can't figure it out

    Uploading a very simplified version of what I'd like it to look like
    Attached Files Attached Files

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

    Re: Is it possible to put value/calculated fields between Row field in a flat pivot table?

    Here's what I would do.

    1. Use Data->Get & Transform->From Table/Range to load raw data to Query Editor.
    2. Once in query editor select Client columns and unpivot.
    3. Right click on Attribute column and split by right most delimiter (space).
    4. Select Attribute.2 & Value columns and Pivot.
    5. Perform some clean up and rename columns as needed.
    6. Add custom column "MonthName" with following function.
    Please Login or Register  to view this content.
    7. Change data types as needed. Close & Load To... and check connection only and load to data model.

    Complete M:
    Please Login or Register  to view this content.
    8. Insert Pivot and use table from Data Model.
    9. Use MonthName & Product Name as Rows. Client as Columns.
    10. Right click on Data Model Table. And add following measures.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    11. Add all measures to Values field and give it friendly/custom name.
    0.JPG

    See attached.

    EDIT: I forgot to mention that you need to turn on "Subtotal" for MonthName in Row field.
    Attached Files Attached Files
    Last edited by CK76; 11-08-2019 at 11:27 AM.

  7. #7
    Registered User
    Join Date
    10-01-2012
    Location
    Canada
    MS-Off Ver
    Office365 32-bit
    Posts
    16

    Re: Is it possible to put value/calculated fields between Row field in a flat pivot table?

    Awesome! Thank you. I didn't realize you could use the measure formulas in this way in order to affect the way the subtotals are or are not displayed. That just blew my mind!

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,906

    Re: Is it possible to put value/calculated fields between Row field in a flat pivot table?

    You are welcome

    PowerQuery and Data Model/DAX are very powerful tool in combination. And it's transferable across MS BI space (SSAS, PowerBI, Excel, Machine Learning via AutoML/Dataflow)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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