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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,623
Members
417,101
Latest member
amoverton2

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
Top