Forum Discussion
Excel Dashboard
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.
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.
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?
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))
- 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.lucykennedy66 Glad I could help!
