DAX for average cost - snowflake schema

[TapZxK]

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

[v-juanli-msft · Accepted solution]

Hi @TapZxK 

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]

Hi @TapZxK 

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.

[TapZxK]

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