Excel Dashboard

[lucykennedy66]

Hi All,

I was looking for some advice on a dashboard I am working on.

I have created a dashboard with Pivot tables and graphs, along with running totals at the top that are using a sum formula based on cells within a pivot table.

The issue I am having is every time I filter the pivot tables the running totals at the top are incorrect as they are pulling from cells that are changing every time I filter using the pivot tables and slicers. 

I can't seem to understand how I stop that from happening and only have the required totals at the top.

Example:

When the pivot tables are not filtered by totals are correct:

Open = 396

Closed = 367

Affected = 56

Not affected = 4

However, when I filter using a slicer my open totals will move to close as an example and my affected totals seem to double. 

Any help would be appreciated.

thanks 

Lucy 

[#2]

@lucykennedy66 

Without seeing the actual spreadsheet here, it's hard to give any advice. That's I'm sure why you've had no reply despite over 80 views.

Is it possible for you to post a copy of the spreadsheet--so long as nothing in it is confidential or proprietary? If you can' do it here, post it on OneDrive or Google Drive and post a link. The actual sheet is far FAR more helpful than an image so please do your best to post a spreadsheet that illustrates the phenomenon you're describing.

[#3]

@mathetes 

Thank you for replying I have attached an example here as the actual sheet is restricted to my Work laptop.

I have running totals on a dashboard that are reading from a sum created using the pivot table but the totals change when the pivot table is filtered. Hopefully, this example sheet is understandable.

https://docs.google.com/spreadsheets/d/1qt7nKbHrO74cLRrf8MmTs-0lDdhzzSbm/edit?usp=sharing&ouid=10660... 

The Worksheet is called example 

[#4 · Accepted solution]

@lucykennedy66 Rather than using SUM formulas that point to ranges inside pivot tables, make use of the GETPIVOTDATA function. Start by just selecting a cell and begin typing = and point to a column total, e.g. for "open".

It automatically creates the following formula:

=GETPIVOTDATA("Quantity",$A$3,"status","open")

meaning get the quantity from the pivot table that starts in A3 where the status equals "open".

when you now slice the pt to only show closed cases, you get a #REF! error,. because there no longer are open cases in this pt. Capture that by wrapping the entire formula in IFERROR like this:

=IFERROR(GETPIVOTDATA("Quantity",$A$3,"status","open"),"")

As a final step you can make it more dynamic by pointing a cell that contains the word open like in J9. Then you can drag the formula down, but be sure that the words J10 and J11 are spelled correctly.

I've done that in the attached file.

[#5]

That worked perfectly, I also understand now why it wasn't working. Thank you so much for your help, and thanks again to you both for responding. I am still learning a lot about excel which is great, but I am still a beginner when it comes to formulas.

Lucy.

[#6]

@lucykennedy66 Glad I could help!

[#7]

@Riny_van_Eekelen 

Hi Riny

I always thought that the GETPIVOTDATA way of hard-wiring instances of the dimensions cause users to hate the function.  I always tended to replace hard-wired values by an array of all possible values and use CSE.  Now one can go further and filter out the instances that did not return values.

= LET(
      statusHdr,      UNIQUE(Table1[status]),
      extractedData,  GETPIVOTDATA("Quantity",$A$3,"status",statusHdr),
      summary,        HSTACK(statusHdr, extractedData),
      FILTER(summary, ISNUMBER(extractedData))
  )

Do you have thoughts on what is the most efficient way to link the Slicer settings to the to the list of status settings for which results have been calculated?

[#8]

@Peter Bartholomew

Hi Peter,

I like your you all-in-one ultra dynamic solution. Would not have thought about that myself.

As far as linking the calculation directly to the slicer setting, avoiding the need to read the data table for all possible status values and then filter out those not selected would be to set-up a 2nd (dummy) pt, connected to the same slicer and format it without headers or total and only display the status. Give a large enough range a name (hidden below the slicer), e.g. "sliced". Not sure though that this is mor efficient :))

=LET(
    selection,     FILTER(sliced,sliced<>0),
    extractedData, GETPIVOTDATA("Quantity",$A$3,"status",selection),
    HSTACK(selection,extractedData))