+ Reply to Thread
Results 1 to 14 of 14

Pivot Table - Show % with Yes for Month

  1. #1
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Question Pivot Table - Show % with Yes for Month

    I have a large set of data that has ticket information, when created, account, who worked it, when closed, etc. One of the columns is SLA Resolution Met, and it's Yes/No/Blank. What I would like to do is have a pivot table with the engineer assigned, and the month ticket was completed, and show the percentage of SLA Resolution Met. Currently when I create a pivot table it just does a count. Trying to use the show values as options don't get what I need. What I would like it to show is columns of engineers, headers of Months, and the data would be % of tickets in that month by engineer that were resolved (Yes) within SLA. The reason I am doing this as a pivot table instead of formulas, is to make my metrics more manageable. This is just one metric out of many that I am tracking, and doing it all with formulas takes forever to updated and locks up Excel for 10+ minutes. It is a large data set.

    Any help is appreciated.

    Thanks
    Attached Files Attached Files
    Last edited by reem01; 01-03-2018 at 06:10 PM.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,884

    Re: Pivot Table - Show % with Yes for Month

    maybe move SLA to row field and use Design - Report Layout - Show in Tabular Form
    or
    attach example excel file
    sandy (NO CAPS)
    Excel can do everything but still doesn't read user's wishful thinking
    The ready made solution will not teach anyone anything

    A logical description of the problem is the basis of dialogue and a rapid solution. Nobody sits in your head to understand your way of thinking
    Note: English is not my native language
    Keep It Sophisticatedly Simple

  3. #3
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: Pivot Table - Show % with Yes for Month

    Thanks for you quick reply Sandy. I have added the attachment to the original post. Tried your other option, but seemed to give me the same issue with show values as.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,884

    Cool Re: Pivot Table - Show % with Yes for Month

    here is, but I don't know what you want to calculate via field Values so add appropriate itmes by yourself
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: Pivot Table - Show % with Yes for Month

    Thank you Sandy.

    What I would like to show is in those months, what was the % of tickets that were completed within the SLA. So what % of cells have the Yes in the SLA Resolution Met column for engineer 1 in Jan, Feb, Mar, etc. Does that make sense?

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,884

    Re: Pivot Table - Show % with Yes for Month

    try drag SLA again but into Values field (it should show Count....)
    then use dropdown there and select Value Field Settings
    then

    vfs.jpg

    and select from (2) option what you want to see

  7. #7
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: Pivot Table - Show % with Yes for Month

    Sandy, I haven't been able to find a value in that list that will work. It counts all the entries, Yes and No, and doesn't give me an option to show values as % for that month/engineer. In my example spreadsheet, if you look at engineer 2, for January they are 66% for SLA Resolution Met. But I can't figure out a way to show that. Like I mentioned in the original post, I currently do it with formulas and it works, but with all the other metrics, it makes my spreadsheets very unruley.

    I really appreciate all your assistance.
    Attached Images Attached Images

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,884

    Re: Pivot Table - Show % with Yes for Month

    Sorry mate but I don't see any percents in your file from post #1

    maybe attach an example of what you want to achieve created manually (by formula)

    edit:
    eng 2 - yes - Jan - 66.67% ?
    Last edited by sandy666; 12-28-2017 at 05:31 PM.

  9. #9
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: Pivot Table - Show % with Yes for Month

    Here is another sample spreadsheet with what I would like it to look like, the formula that I currently use, and a sample data set. Hope this helps explain it better.
    Attached Files Attached Files

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,884

    Cool Re: Pivot Table - Show % with Yes for Month

    see this one
    (sorry but this is with PowerQuery and PivotTable)

  11. #11
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: Pivot Table - Show % with Yes for Month

    Sandy, thank you so much. I do not know PowerQuery and PivotTable, but with your example, I have gotten about 85% there. What I have not yet figured out is how to make that pivot table you have that shows the percentages. When I click on it, I don't see the normal pivot table options on the right side. Can you let me know what you did there?

    Thanks

  12. #12
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: Pivot Table - Show % with Yes for Month

    I found the pivot table options and have setup my data similarly. In your query you are changing the date field to Month. The unfortunate side affect is that it now combines the 2016 and 2017 months together. I am looking for a way to change that to incorporate the year. If you know, or anyone else knows how to change the below query, I would appreciate it.

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

  13. #13
    Registered User
    Join Date
    04-07-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    27

    Re: Pivot Table - Show % with Yes for Month

    I have figured this out. Learned a lot, thank you very much.

    I removed the Extract Month Name query, then that allowed me to add Complete Date (Month) and Complete Date (Year). I then needed to change the Sum of Count values shown as %pf Parent Total. That gave me what % of time each engineer met the SLA in that month.

    Once again, thank you for all your assistance.

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,884

    Re: Pivot Table - Show % with Yes for Month

    Good to see you got what you want by yourself
    Have a nice day

+ 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