PowerPivot CALCULATE Function not responding to filters

[nheitzman]

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?

 

[nheitzman]

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:

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

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.