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!
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!