+ Reply to Thread
Results 1 to 2 of 2

Double Counting Combined Value Cells

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Pro Plus 2016

    Double Counting Combined Value Cells


    I'm trying to make a pivot table out of data where some entries have a combination of values. For example, let's say my column title is fruit, so most cells are either apples, oranges, or strawberries. Some entries though are labeled as 'apples/oranges' or 'oranges/apples', etc. When I go to make a pivot table, those combined entries with 'apples/oranges' are listed as its own subcategory. However, I just want that cell to be counted in apples once, and then again under oranges. Is this possible to do with pivot tables? And if so, how?
    Last edited by richx; 08-16-2019 at 01:49 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus

    Re: Double Counting Combined Value Cells

    Not by standard pivot table. No. And do you really want to double count?

    There are 2 ways to handle this.

    1. Use PowerQuery to transform data then load to data model.
    This is my recommendation. You can use GUI tool in query editor, to split column by delimiter into rows and replicate data for other columns into 2 (or more) rows.
    You can then load it to data model and base pivot table off of it.

    2. Use PowerQuery to load data to model without transformation.
    You will then use DAX measure to construct calculation. However, this can get complex depending on your data structure and use case.

    Either case, if you want further help. I'd recommend uploading sample workbook and manual mockup of your desired result.

    To upload, use "Go Advanced" button, follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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