# PowerPivot CALCULATE Function not responding to filters

#### nheitzman

##### New Member
Hello All,

I am fairly new to PowerPivot. I have a table called Tran with Fiscal Year, Period, and Amount, and other descriptive fields. I am trying to make a PowerPivot table which will have columns Fiscal Year, Period, Amount, Total Amount for a given fiscal year, Amount / Total Amount for given fiscal year. I am currently using this formula:

=CALCULATE(SUM([Amt]),FILTER('Tran','Tran'[FY] = EARLIER('Tran'[FY])))

This correctly total provides the yearly total for a given fiscal year. Unfortunately, when I use slicers with my other descriptive fields, these totals do not update. I'm hoping when it filters for the totals to update based on this filter as well.

Can anyone help me?

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### nheitzman

##### New Member
I was able to solve my problem, but thought others might want to see the solution.

I had been trying to do the calculation in a calculated column, not a measure. I built two measures as follows:

Code:
``Total Amt = sum([Amt])``

The next measure, calculated the total based on Fiscal Year. The key here was to make it IGNORE the Period Column

Code:
``CALCULATE([Total Amt],all('Tran'[Pd]))``

The all('Tran'[PD]) filter ignores the period column, giving me an original total for a fiscal year that is filterable by other qualitative characteristics.

Replies
1
Views
182
Replies
0
Views
962
Replies
0
Views
647
Replies
8
Views
204
Replies
3
Views
2K

Threads
1,190,587
Messages
5,981,799
Members
439,735
Latest member
Tony_P

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

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