+ Reply to Thread
Results 1 to 10 of 10

Need my Dashboard to change when slicer selected on pivot

  1. #1
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    389

    Need my Dashboard to change when slicer selected on pivot

    Hi I am looking for some help, I have made a start to my dashboard and want it to show me totals of whatever sector is selected.

    at the moment I can only get Clyde by date/s - I would like it to be able to change to what sector and date selected

    also is there a better way to show it other than using slicers

    please help

    Dougers1
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Kaysville, UT
    MS-Off Ver
    Office 365
    Posts
    118

    Re: Need my Dashboard to change when slicer selected on pivot

    For the cell in D6 you need to eliminate the "Sector","Clyde Sector", piece. The slicer does that so you shouldn't reference it twice. The formula in that cell should look like:
    =GETPIVOTDATA("Status",'Sector View'!$A$4,"Status","Filled")
    Then it works with the slicers. The other fields are problematic because the pivot table has the fields you are referencing as rows. Try making them columns.

  3. #3
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013; 365
    Posts
    221

    Re: Need my Dashboard to change when slicer selected on pivot

    I remember trying to do dashboards with charts from Pivot Table and sometimes getting into "trouble" with GetPivotData, so I used actual ranges (eg H2:H10) or I stopped using Pivots and used Tables instead.
    Yes, Pivots are useful, but in this case you could use simple Tables in my opinion.

    In your sample file I created a Table (with CTRL+T) in your Data sheet (Table1), added slicers for Sector and Date and in the Dashboard sheet I added the Table version slicers. Then added formulas to count data in Table1 when it is filtered via slicers and I got all the information.

    See attached file. Let me know if it's okay like that.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    389

    Re: Need my Dashboard to change when slicer selected on pivot

    Quote Originally Posted by DavidBowman View Post
    For the cell in D6 you need to eliminate the "Sector","Clyde Sector", piece. The slicer does that so you shouldn't reference it twice. The formula in that cell should look like:
    =GETPIVOTDATA("Status",'Sector View'!$A$4,"Status","Filled")
    Then it works with the slicers. The other fields are problematic because the pivot table has the fields you are referencing as rows. Try making them columns.
    try the column bit but still struggle to get other data to update ?

  5. #5
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    389

    Re: Need my Dashboard to change when slicer selected on pivot

    Quote Originally Posted by Mrrrr View Post
    I remember trying to do dashboards with charts from Pivot Table and sometimes getting into "trouble" with GetPivotData, so I used actual ranges (eg H2:H10) or I stopped using Pivots and used Tables instead.
    Yes, Pivots are useful, but in this case you could use simple Tables in my opinion.

    In your sample file I created a Table (with CTRL+T) in your Data sheet (Table1), added slicers for Sector and Date and in the Dashboard sheet I added the Table version slicers. Then added formulas to count data in Table1 when it is filtered via slicers and I got all the information.

    See attached file. Let me know if it's okay like that.
    This works fine I will have a go at adding more data and charts - Thanks

    how did you get slicer to work with table ?

    is it possible to add charts that will show the fiiled and unfilled count for each sector when selected - or all sectors separate , a percentage fill rate would also be good ?



    David
    Last edited by dougers1; 11-05-2019 at 06:07 PM.

  6. #6
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013; 365
    Posts
    221

    Re: Need my Dashboard to change when slicer selected on pivot

    Slicers work by default with Tables and Pivots.
    Of course if the Tables are made as stated using CTRL+T and not just adding cell borders like in Word

    For work I made dashboards with charts using a combination of Table and Pivot, but didn't make Pivot charts because I ran into some formatting problems - for some reason when working with Pivot charts and Slicers, the formatting of the chart wouldn't be kept between various Slicer option changes (see animated gifs below).

    So I made my charts manually by adding series from ranges of data "behind" the Pivot tables.

    1 - Pivot chart made from Pivot table - you can see that if you select 2 years it ruins the formatting, which remains ruined even after going back to one year selection (data range is PivotTable2)

    pivot.gif

    2 - the chart is not Pivot chart but has the range "behind" the Pivot table as source - you can see that the formatting is kept even if selecting multiple years then going back to one (data range is $O$6:$Q$18, where PivotTable2 is)

    nopivot.gif

    So to finally answer your question it is possible by using both Tables and Pivot tables, but it prevents some problems if you make normal charts instead of Pivot charts, even from Pivot tables as chart sources.

    Just my opinion, some people here might know a solution using the Pivot chart as well.
    I had to make normal charts from Pivots because I could find no other way to keep my formatting.

  7. #7
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    389

    Re: Need my Dashboard to change when slicer selected on pivot

    Quote Originally Posted by Mrrrr View Post
    Slicers work by default with Tables and Pivots.
    Of course if the Tables are made as stated using CTRL+T and not just adding cell borders like in Word

    For work I made dashboards with charts using a combination of Table and Pivot, but didn't make Pivot charts because I ran into some formatting problems - for some reason when working with Pivot charts and Slicers, the formatting of the chart wouldn't be kept between various Slicer option changes (see animated gifs below).

    So I made my charts manually by adding series from ranges of data "behind" the Pivot tables.

    1 - Pivot chart made from Pivot table - you can see that if you select 2 years it ruins the formatting, which remains ruined even after going back to one year selection (data range is PivotTable2)

    Attachment 648488

    2 - the chart is not Pivot chart but has the range "behind" the Pivot table as source - you can see that the formatting is kept even if selecting multiple years then going back to one (data range is $O$6:$Q$18, where PivotTable2 is)

    Attachment 648487

    So to finally answer your question it is possible by using both Tables and Pivot tables, but it prevents some problems if you make normal charts instead of Pivot charts, even from Pivot tables as chart sources.

    Just my opinion, some people here might know a solution using the Pivot chart as well.
    I had to make normal charts from Pivots because I could find no other way to keep my formatting.
    would you be able to help me a little - I also need to say that my work has excel 2010 and not latest

  8. #8
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013; 365
    Posts
    221

    Re: Need my Dashboard to change when slicer selected on pivot

    I'll see what I can do with the filee when I get home in the afternoon.

  9. #9
    Forum Contributor
    Join Date
    11-26-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    389

    Re: Need my Dashboard to change when slicer selected on pivot

    Thanks anything you can do will be a great help to me

  10. #10
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013; 365
    Posts
    221

    Re: Need my Dashboard to change when slicer selected on pivot

    I made something with a Pivot table and Slicers and in the way I said before: added series manually to the chart, didn't make a Pivot chart.
    If you check the data source of the chart you will see that it refers to ranges in the Data sheet and not the Pivot table, eg. =Data!$K$9:$K$12

    Made some comments in the Dashboard sheet for both my versions as you won't be able to use both at the same time.
    But I think that now you can replace my first version of dashboard as the 2nd version with the chart covers those results as well. Unfortunately I wasn't able to move the Grand Total (Total Emp) first like in your original version.

    Also made a dynamic chart title, you'll see it in the Data sheet.
    To assign it to the chart, I just added the chart title (because by default the chart is made without one), then clicked on the title and went to the formula bar and wrote = and then selected the desired cell.
    If you click the title and check the formula bar you will see it's =Data!$N$5 and in that cell there is a formula to make the title look nice and dynamic depending on Slicer selection: =J4&": "&IF(K4="(All)";"All";K4)&" --> "&J5&": "&IF(K5="(All)";"Filled and Unfilled";K5)

    I hope you like it and it helps you.

    If you have more questions feel free to ask.

    Later edit:
    What I forgot to mention is thatin order to keep all columns even if the value was 0 (like in the case of Agency), I had to do a trick proposed by user Kevin Rudolph on social . technet . microsoft . com. In the Pivot table (because it wasn't showing zero values) I did that by:
    clicking on the field in which you want to keep zeros resident (e.g. Agency), click on "Field Settings" then the "Layout & Print" tab, then check the "Show Items with no data" box.
    Attached Files Attached Files
    Last edited by Mrrrr; 11-07-2019 at 04:49 PM.

+ 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. Replies: 1
    Last Post: 06-12-2019, 03:48 PM
  2. [SOLVED] need graphs to change when another cell is selected - Dashboard
    By dougers1 in forum Excel General
    Replies: 6
    Last Post: 04-12-2019, 12:50 PM
  3. Pivot Chart Slicer show total when all options are selected
    By atl57 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-05-2019, 01:15 PM
  4. code that will count the number of items selected in a slicer on a pivot table
    By asmi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2017, 12:57 AM
  5. [SOLVED] Run macro to change pivot table row field when user selected a slicer item
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2016, 09:44 AM
  6. Changing Pivot Chart Y-Axis when different slicer is selected
    By future_career in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2016, 05:22 PM
  7. [SOLVED] Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer
    By MBeedle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2013, 01:47 PM

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