Forum Discussion

silkwire's avatar
silkwire
Frequent Visitor
7 years ago
Solved

Calculate() - Issue filtering related table

Hello - 

 

I have two tables: tblProject and tblAllocation

 

 

 

 

 

 

 

 

 

tblAllocation stores all information relevant to a monthly allocation in relation a project. Each record references a unique ProjectID from tblProject. 

 

Each project record in tblProject has a 'Strategy' flag. 

 

I want to measure what % of our utilization is going against projects that are part of the strategy.

 

This is the formula I thought to use, but it's not properly filtering out non strategic projects:

 

%Utilization to Strategy = Divide(Calculate(Sum(tblAllocation[Utilization]), Filter(tblProject, tblProject[Strategy]=True())), Sum(tblAllocation[Utilization]))
 
How do I properly filter based on a related table?
 
tblProject
ProjectIDStrategy
1TRUE
2FALSE
3FALSE
  
tblAllocation
ProjectIDUtilization
110
115
130
210
210
320
330
310
35
 

In this scenario, I would want the measure to be 55/140 = 39%. Since only ProjectID 1 has a strategy=true.

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi silkwire ,

    I have a little confused about your requirement.

    By my test with your sample data and your measure, it seems that the  result of measure is 39% as your desired.

    Do I missing anything? 

    Best Regards,

    Cherry

     

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi silkwire ,

    I have a little confused about your requirement.

    By my test with your sample data and your measure, it seems that the  result of measure is 39% as your desired.

    Do I missing anything? 

    Best Regards,

    Cherry

     

    • silkwire's avatar
      silkwire
      Frequent Visitor
      7 years ago

      Thanks for the reply. I did the same thing as you and it worked... then I realized that there was a page filter set in the report I was working in that filtered out all false values. So, problem solved. Thanks.