Creating a total budget column.

[jeancake]

I have a power pivot table with two fact tables 2024 actual and 2024 budget. I have three dimension tables product, region and a calendar table.

The pivot table has the following:

Rows
Region
Product

Measures Sales
Budget
Variance

YTD Sales
YTD Budget
YTD variance
Slicer the are tofor month:

I ould like to add a column showing the total annual budget for the budget and region to show how close each product is YTD to the total annual budget ,but I'm struggling to rite the formula

.

 

[Matt Allington]

To know how to solve this problem, you need to first think deeply about how a pivot table works. Rows, columns and slicers all put filters on your dimension tables (at least, these things SHOULD come from your dimension tables). So the reason Budg $ is showing the Dec 24 number is because there is a filter on Dec 24 coming from your slicer. If you want another measure to show the full year budget, you will need to remove this filter from Dec 24 and reapply a filter for the year 2024. How you do this will depend on the calendar table you have. Regardless, you will need to use CALCULATE. CALCULATE is the function that allows you to change the natural filtering behaviour of the pivot/model.

You should make sure your calendar table has a year column as well as the mmm-yyyy column. I would then first capture what year is being filtered, then remove the mmm-yyyy filter, then re-apply the year filter.

Something like this (it will depend on your calendar)
Full year =
VAR SelectedYear = max(calendar[year])
RETURN CALCULATE([Budg ($) Measure], All(Calendar),Calendar[Year]=SelectedYear)

 

[jeancake]

Thank you, Matt. That was very helpful.