Filter is not applied to a measure in the detail table

[rugubara]

I've come across a strange behaviour of Power Pivot related to measures on fields of detail tables. I have the following simple model:

Grades (Grade code, Target Utilization)
Team (Name, Grade code) <links to the Grades on Grade Code>
Forecast (Name, Period, value, MaxValue) <links to Team on Name>

I calculate the utilization measure as sum(value)/sum(MaxValue) and I'd like to define a KPI on utilization based on the target utilization of the corresponding grade.
So I deline the TargetUtil% measure as min(Target Utilization).

I make the pivot table of this model and put Names to the rows, and periods to columns and TargetUtil% as Values. To my astonishment, is shows the minimal % of all records in the Grades table everywhere i.e. it doesn't filter the records for the Name to get the value of TargetUtil% that is associated with the grade the particular name has. If I remove the period from the columns - nothing changes. Still the global min is shown.

When I put the Name on the rows, the Grade from the Grades on the columns and my TargetUtil% measure to values, I can see why it works this way. All cells are filled. Cartesian of the Name and Grade is in the dataset. I wonder why it is so and how to make sure the link in the data model is honored and filtering happens ?

The sample file can be downloaded here MrExcelSample.xlsx

 

[sanalytics]

Your Grade_Table is on One side and Team_Table is on the many side. So basically your Grade_Table is filtering Team_Table. Power Pivot only supports single direction so, you should get same number if you take Name column from Team_Table.
Create the Target utilization column in Team_Table by using Related function.
Then use that column as Min(Target Utilization)

Let me know if you need more help

Regards
sanalytics