Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TapZxK
Helper II
Helper II

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

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

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
Community Support
Community Support

Hi @TapZxK 

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

If you import data and have such data:

Capture10.1.JPGCapture11.JPG

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" )
    )

Capture12.JPG

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.