Help with filtering a table based on another table's context

dwells

New Member
Joined
Apr 14, 2015
Messages
3
I am working on a budget project in Power Pivot. I have four tables. Simplifying a bit Table 1 is tblTransactions. Fields are Date, Transaction, Amount, Category. Table 2 is tblBudget. Fields are Date, Amount, Category. Table 3 tblDate. Fields are Date, Month, Year, MonthNum (for sorting). Table 4 is tblCategories. Fields are Category, TopCategory, Group. Relationships should be obvious Date is related through tblDate, and Category is related through tblCategories. The data set has several years worth of data.

One of my Pivot Tables has Month To Date Actual, Budget and Variance, and Year to Date Actual Budget and Variance. Those are based on several calculated measures that filter and summarize the data.

However, because my Pivot Table use Category, Top Category and Group from tblCategory as the rows, it contains multiple categories that do not have data from the current Month or current Year. I would like Pivot Table to only include the categories from tblCategories that have data in the tblBudget and tblTransaction consistent with the calculated filters for MTD and YTD. I can't seem to do it on the Pivot Table itself, and I tried to create a calculated measure using FILTER, CONTAINS, CONTAINSROW and a few more that I am now forgetting. I either can't get the syntax right based on examples I found online, or I am just going down the wrong alley. Any help would be appreciated.
Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top