Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @TapZxK
Hi @TapZxK
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" ) )
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |