Non-uniform Data Set for Pivot Table/Chart

[tadpole xp]

Hello all,

First of all a big thanks to all users who have posted such great answers to past questions as well as those who posted those questions to begin with.

I have searched for a macro/VBA free solution to this problem for a while with no luck. Hoping I will strike gold on my first post here.

The below is a snapshot of how our data is organized:

Period	Sales Mgr	Sales Person	Customer	Brand	Current MTD Sales QTY	Current MTD Sales Goals	Qtr Goals	Qtr
Jan	John A	Mike B	Store C	Brand X	10
Jan	John A	Mike B	Store D	Brand Y	15
Jan	John A	Mike B	Store E	Brand Z	20
Jan				Brand X		500
Jan				Brand Y		200
Jan				Brand Z		100
		Mike B		Brand X			200	Q1

<colgroup><col style="width: 43px"><col width="65"><col width="84"><col width="62"><col width="53"><col width="146"><col width="154"><col width="98"><col width="60"></colgroup><tbody>
</tbody>

We constantly update sales info consisting of: Period, Sales Mgr, Sales Person, Customer, Brand, and Current MTD Sales QTY. There are overall brand goals by period, which do not go down to the mgr, sales person, or customer lvl. There is also a separate set of quarterly goals. This feeds a dashboard consisting mostly of pivot charts and tables.

The issue we are trying to compensate for are the blank cells for non value columns. The dashboard is controlled with a panel of slicers and given the above data, the slicer for 'Period' has '(blank)' has an option because of the last row. Slicers for 'Sales Mgr', 'Sales Person', and 'Customer' all have '(blank)'s as well. We need the pieces of data on the rows associated with the '(blanks)'s, however we do not want to have to always remember to select '(blank)' in its respective slicer in order for them to show. Is there a way to have excel ignore this as a filter option and still represent those rows' data? Open to any and all suggestions.

Thank you in advance!

 

[tadpole xp]

*There are a separate set of quarterly goals for sales persons by brands.