Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX for average cost - snowflake schema

Hi Everyone,

 

I'm new to Power BI and DAX, I have been working with it for approx 2 weeks and I have run in to a problem.

 

I have two look up tables joined as a snowflake schema. 

 

1. The Employee_Lookup table contains employee names (Enterprise ID column) that are currently active & Inactive on our project. This is determined by a column named - "Resource Status" and values there are Active / Offboarded.

2. The LCR_Lookup table contains Hourly Cost rates for the people, this Table contains everyone who has ever charged any hours towards our project. the two tables are joined by Enterprise ID column as both tables contain this information.

 

I'm looking for a way to calculate Average Hourly Cost of only employees that appear Active and Ignore the ones who are Offboarded. Can't figure out how to do so.

 

I would greatly appreciate your help.

 

Best Regards,

Kris

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case?
    If not, please let me know.
     
    Best Regards
    Maggie

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case?
    If not, please let me know.
     
    Best Regards
    Maggie
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Do you import data into Power BI or sue direct query?

    If you import data and have such data:

    Please create a measure

    Measure =
CALCULATE (
    SUM ( LCR_Lookup[Hourly Cost rates] ),
    FILTER ( ALL ( Employee_Lookup ), Employee_Lookup[Resource Status] = "Active" )
)
    / CALCULATE (
        DISTINCTCOUNT ( Employee_Lookup[enterprise id] ),
        FILTER ( ALL ( Employee_Lookup ), Employee_Lookup[Resource Status] = "Active" )
    )

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Maggie,

       

      Yep, I do import the data. 

      I will give your advice a shot and let you know how did it go. :) 

       

      BR,

      Kris