 # Pivot Table/Slicers

• Hi,

I am trying to create a dashboard which compares actuals sales VS budget also combined with slicers so people can make their own selections.

I have two tabs, one for the actual invoices to date which will be updated every day, and the other one for budget which is set at the start of the year.

The pivot table and the slicers from "Invoices" tab and then I use the formulas to calculate the Budget and the %Achieved.

However, the issue I have come across is that to date I have no invoices to Sainsburys or West Region or certain towns but I still want them to appear in the slicers and the dashboard so I could see the full view of the budget set for the month.

I would be very grateful if you could help me to find a solution to overcome this problem please.

I have attached my sample data.

## Files

• Thanks Roy, that seems to have done the trick I have been researching about possible combining the two tables first and only then creating a pivot table, but that option looked too complicated for me • Sorry, I have one more question how could I adjust my formula in Budget - Column H so that once it finds "Grand Total" in Column A it sums everything above that in Column H?

Thanks

Jurate

• Yes, I want the total for the Column H, but the position of it will vary depending on which slicers will be selected, so I was trying to come up with a formula that references to "Grand Total" in Column A.

• Hi Roy, I am not sure if understand either of you suggestions.

Column H is not part of the pivot table and comes from a different source data.

I have already the formula below in Column H to show the budgeted figures.

=IFERROR(IF(A16<>"",VLOOKUP(E16,Budget!\$A:\$I,9,FALSE),""),"")

I was thinking of a way to edit this formula, say if A16="Grand Total" then sum column H...the sum total in H should be in the same row as "Grand Total" in Col A - which will vary with slicer selections.

So far I had no luck so not sure if that is even doable Any further suggestions are much appreciated.

• I think PowerPivot would be the best way to do this. I've tried this formula but it doesn't seem to work

=IF(OR(A29="Grand Total",A29=" "),SUM(\$H\$16:H29),IFERROR(VLOOKUP(E29,Budget!\$A:\$I,9,FALSE),""))

Your Budget Formulas aren't dynamic because you have to amend the column ibn the VLOOKUP

• hi Roy,

I have managed to get the total sum using the below formulas, but they only work when no slicers have been selected.

Is it possible in the offset function instead of where I have "14", to make it count the number of cells between \$A\$16 to "Grand Total"?

=IFERROR(IF(\$A30="Grand Total",SUM(\$H\$16:\$H\$29),VLOOKUP(E30,Budget!\$A:\$I,9,FALSE)),0)

=IFERROR(IF(\$A30="Grand Total",SUM(OFFSET(\$A\$16,0,7,14)),VLOOKUP(\$E30,Budget!\$A:\$I,9,FALSE)),"")

How do I make budget formulas dynamic? - sorry it is all a bit new to me • hi Roy,

I have attempted to create PowerPivot, however not sure why the budget figures are not updating properly just showing the total amount on each line.

I think I have created a relationship between the different tables but still don't seem to work. Could you please have a look at the updated Workbook - Sheet "New Dashboard" and advise what I am doing incorrectly?

Thanks