+ Reply to Thread
Results 1 to 3 of 3

Pivot Table - Multiple Data Columns issue

  1. #1
    Registered User
    Join Date
    01-04-2019
    Location
    London
    MS-Off Ver
    2017
    Posts
    3

    Pivot Table - Multiple Data Columns issue

    Hi all,

    I have an incident report in which I am trying to track the date Incidents were Created and the data Incidents were Resolved.

    I would like to create a Pivot Table which has the Date in the Rows and an accurate Count of Created and Count of Resolved in the Values columns.

    As you can see from my sheet, the Pivot Table I have produced is not capturing this data accurately. I can think of workaround e.g, producing 2 pivot tables and v-lookuping from another table to capture both the Count of Created and the Count of Rdsolved, but I was wondering how I could do this within a Pivot Table?

    Thanks!

    Excel Q.xlsx

    Excel Q image.PNG

  2. #2
    Valued Forum Contributor Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,125

    Re: Pivot Table - Multiple Data Columns issue

    the resolved dates do not count correct because it has blanks and blanks are not counted
    You need to put the dates in the rows section not the values section. and use the incidentnumber for an accurate count. blanks are not
    then you can see the resolved date per creation date. eighter in rows or in columns by moving resolved date eighter to rows or columns section.
    Attached Files Attached Files

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,734

    Re: Pivot Table - Multiple Data Columns issue

    This is where Power Pivot will be useful.


    First: resize Table1. It doesn't need to include all rows.

    Then use Power Query to add Created Date and Resolved Date fields (date only, excluding time) to this table. Name this query Incidents, and Load To Data Model:
    Please Login or Register  to view this content.

    Now in PowerPivot, create a Date table (Design > Calendars > Date Table > New), and create an active relationship to Incidents[Created Date], and an inactive relationship to Incidents[Resolved Date], on field Calendar[Date].

    Then you can create DAX measures:

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Now you can create your pivot table, using Calendar[DateHierarchy] in Rows, and [Incidents Created] and [Incidents Resolved] as measures.

    See attachment for worked example.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ 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