Measures Not Updating with Filters

[MacFletch]

When I selected a filter inside the table, several of my calculated fields do not filter. For clarity, by filter, I mean making a selection from the drop down arrow in the column header.

Any thoughts? Here is one of the formulas:

MostRecent52WeekAvg:=calculate ([Total Calls], filter(all(CallData), CallData[WeekID]<= max(CallData[WeekID]) && CallData[WeekID]>=max(CallData[WeekID]) - 51)) / 52

 

[Matt Allington]

When I selected a filter inside the table......the drop down arrow in the column header.

What "table"?
What "Column header".

It is not clear what you are talking about.

Do you mean a Pivot Table? Can you post an image or something?
What is the formula for [Total Calls]?

 

[MacFletch]

I apologize for the lack of clarity.

The table I refer to is CallData in my powerpivot data model. I need to be able to filter by location in the data model or in a pivot table. Whether I filter to a single location in either the data model or a pivot table, the measure above does not change. It stays the same value as it is when all filters are off.

The formula for [Total] calls is Total Calls:=sum(CallData[Calls])

The data table has location, week number, and calls. There are 189 weeks (3.6 years) of call data per location. The Most Recent 52 Avg formula I initially posted is designed to calculate the average call volume for the last 52 weeks (weeks 138 through 189). It works beautifully for all locations in the aggregate. But when I select a single location, I still get the aggregate 52 week average amount.

I hope this is clearer.

Thanks for any help you can give.

 

[Matt Allington]

Your issue is you have a single table and you are removing the location filter with your all statement. Either create a star schema and remove filters from the lookup tables, or change your formula.

Allexcept(calldata,calldata[location])

The Optimal Shape for Power Pivot Data - Excelerator BI

 

[MacFletch]

Added a star schema and it works perfectly. Thank you for help Matt!