+ Reply to Thread
Results 1 to 8 of 8

Calculated Item results in empty rows Pivot Table

  1. #1
    Registered User
    Join Date
    08-14-2019
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    4

    Calculated Item results in empty rows Pivot Table

    Good day everyone,

    I want to show the trend per week in on time delivery (per carrier). I have added a calculated item column to the pivot table to get the required 'on time' percentage. Unfortunately after using a calculated item I am not able to correctly filter the pivot table: Empty rows are not removed when using the slicers/filters, therefor making the graph unusable.

    Ideally the graph should display the percentage of ON TIME deliveries and move with the slicers, filtering out empty rows. For example: When selecting Shipped Year 2021 it should not show the weeks of 2020. Currently the rows I do not want to see have a #DIV/0! error, I have tried setting all errors to blank cells but it does not make the pivot table more dynamic. Setting the error to a value such as 0 and filtering the column to not show value 0 also does not do anything.

    Desired result (red boxed items should be gone based current filter 2021):
    example.jpg


    From hours of googling I understand this is a common problem with calculated item, I just don't know how to solve it any other way. Please have a look at the example file included containing all relevant columns and sample data.
    Is there a way to get the desired result? I hope I explained clearly.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,831

    Re: Calculated Item results in empty rows Pivot Table

    This proposal, modeled on Sheet2, employs Power Pivot.
    The following DAX measure calculates the percentage on time:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-14-2019
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    4

    Re: Calculated Item results in empty rows Pivot Table

    This is perfect. I have no experience with Power Pivot / DAX and you gave me the tools to look in the right direction, following a course now to get a better understanding.

    Thank you very much JeteMc!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,831

    Re: Calculated Item results in empty rows Pivot Table

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  5. #5
    Registered User
    Join Date
    08-14-2019
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    4

    Re: Calculated Item results in empty rows Pivot Table

    Hi JeteMC and other readers,

    I forgot one crucial step, in addition to ON TIME and LATE values there are also PROCESSING values which should be excluded from the calculation.
    I have changed the last 50 columns to PROCESSING. Could you please provide the formula which will exclude these?

    Thanks in advance!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,831

    Re: Calculated Item results in empty rows Pivot Table

    Try editing the measure to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    08-14-2019
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    4

    Re: Calculated Item results in empty rows Pivot Table

    Thanks a lot JeteMc! Closing the thread.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,831

    Re: Calculated Item results in empty rows Pivot Table

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Pivot table shows irrelevant rows when added a Calculated Item
    By Angjian in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 08-18-2020, 08:56 AM
  2. Pivot Table - Calculated Item adds unnecessary rows
    By elderwand in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-10-2020, 12:14 PM
  3. Creating calculated field and calculated item into a pivot table
    By dvpe in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-12-2019, 12:15 PM
  4. Pivot table with Calculated Item with Two Rows Label
    By vinaynaran in forum Excel General
    Replies: 1
    Last Post: 07-29-2016, 12:06 PM
  5. Replies: 1
    Last Post: 10-06-2009, 04:01 AM
  6. [SOLVED] pivot table formulas for calculated field or calculated item
    By Vicky in forum Excel General
    Replies: 3
    Last Post: 06-06-2006, 12:10 AM
  7. Pivot Table with calculated Item
    By Greg Doucette in forum Excel General
    Replies: 0
    Last Post: 04-12-2005, 02:06 PM

Tags for this Thread

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