Pivot Table - Multiple Data Columns issue

**robertgardner1994** · 06-13-2019, 05:34 AM

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

**Roel Jongman** · 06-13-2019, 05:58 AM

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.

**Olly** · 06-13-2019, 06:04 AM

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.

Pivot Table - Multiple Data Columns issue

LinkBack

Thread Tools

Rate This Thread

Display

Pivot Table - Multiple Data Columns issue

Re: Pivot Table - Multiple Data Columns issue

Re: Pivot Table - Multiple Data Columns issue

Thread Information

Users Browsing this Thread

Similar Threads

Creating a Pivot Table to display columns of data issue

Possible to use slicer to filter data from multiple columns in a pivot table?

Pivot table from multiple columns of data vs date

Pivot Table - Count data From Multiple Columns

Pivot table issue - counting multiple columns

[SOLVED] How to have multiple columns in pivot table data area?

How to have multiple columns in pivot table data area?

Bookmarks

Bookmarks

Posting Permissions