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
Mike_12
Regular Visitor

Trying to create a measure that provides Gap between employee wage and minimum wage

Hello,

I am trying to create a measure that subtracts the minimum wage from the wage of a termed employee.   I am trying to see if a higher gap in pay correlated to longer employment length. The measure will need to reference the employee termination date column in the employee records table (will be blank if the employee is active) and find the correct min wage rate in another table.

 

I have two tables:

   Table 1: Employee Records - Columns used are [termination date] and [pay rate]

   Table 2: MinWage - Columns [MW Date Low], [MW Date High], [MinWageRate]

                Each row contains the start date, end date, and min wage rate

 

Measures already created: 

   Selected MW Date High = MAX('Min Wage Table'[MW Date High])

   Selected MW Date Low = MIN('Min Wage Table'[MW Date Low])

 

I am looking to calculate [Pay Rate] - [MinWageRate] for each employee.  I would then create an average calculation also.  

Any help would be much appreciated!

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @Mike_12

 

Try measure as:

minimum wage = 
CALCULATE(
    MAX('Min Wage'[Min Wage]),
    FILTER(
        'Min Wage',
        'Min Wage'[Date High]>MAX(Employee[Date]) && 'Min Wage'[Date Low]<MAX(Employee[Date])
    )
)

 

Best Regards,

Link 

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-xulin-mstf
Community Support
Community Support

Hi @Mike_12

 

Try measure as:

minimum wage = 
CALCULATE(
    MAX('Min Wage'[Min Wage]),
    FILTER(
        'Min Wage',
        'Min Wage'[Date High]>MAX(Employee[Date]) && 'Min Wage'[Date Low]<MAX(Employee[Date])
    )
)

 

Best Regards,

Link 

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

edhans
Super User
Super User

You probably want to use AVERAGEX. It will  work over a table and average the data. So:

Average Amount =
AVERAGEX(
    'TableName',
    'TableName'[Pay Rate]
        - MINX(
            RELATEDTABLE( 'Min Wage Table' ),
            'Min Wage Table'[MW Date Low]
        )
)

 

But without actual data, hard to really help and validate.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks again for your time and help with this.  I really appreciate it.  However, it still does not provide me with what I am after.  This is my fault, as I believe I have not explained things properly.

 

Here is an example of the employee table:

EmployeeWageTermination Date
Jack112/1/2009
Jill12.55/6/2020

 

I need a measure that will determine the state minimum wage at the time of their termination.

 

  

I created the below measure which returns an accurate head count

   CALCULATE([Term Head Count (Date Based)] ,                FILTER(Sharepoint_Employees,Sharepoint_Employees[TerminationDate]>= [Selected MW Date Low] && Sharepoint_Employees[TerminationDate] < [Selected MW Date High]))
 
Mike_12_1-1618516252388.png

 


 

 
However, I can't figure out how to create a measure that subtracts the state minimum wage from the employee's wage.
From the table above, I need it to subtract the $8.25 min wage from each of the 5,394 employees' wages termed during the period when the state minimum wage was $8.25.
 
Example: 
               Employee Table:  Jack was termed on 2/1/2009.  His wage was $9.25. 
               Min Wage Table:  7/1/2008 to 7/1/2009, the state minimum wage was $7.75.
               9.25-7.75= 1.50
 
Again, I really appreciate the time and effort.

I appreciate your reply.  The measure did not work, but I believe this is my fault for not providing data.  Here is the Min Wage Table:

 

Date LowDate HighMin Wage
1/1/20057/1/2007 $      6.50
7/1/20077/1/2008 $      7.50
7/1/20087/1/2009 $      7.75
7/1/20097/1/2010 $      8.00
7/1/20111/1/2020 $      8.25
1/1/20207/1/2020 $      9.25
7/1/20201/1/2021 $    10.00
1/1/20211/1/2022 $    11.00
1/1/20221/1/2023 $    12.00
1/1/20231/1/2024 $    13.00
1/1/20241/1/2025 $    14.00
1/1/20251/1/2030 $    15.00

 

I need a measure that can use the employee termination date and match it to the appropriate min wage rate during that time, then subtract the min wage from the employee wage.  

 

Example:  Employee - Termed 8/1/2009 - wage $9.25

                 Employee Wage - Min Wage = $9.25 - $8.00 = $1.24 Wage Gap

 

I want to use the measure for wage gap analysis.  In 2011 when min wage was 8.25 and we had an average $2.00 wage gap, our avg employee length of employment was....... In 2020 when min wage was 10.00 and we had a .50 wage gap our avg length of employment was...

 

I already have the employment length measures, I just need a measure to provide the wage gap

@Mike_12 take a look at the PBIX file I've linked to. It returns this:

edhans_0-1618505250004.png

To get this I did a bit of modeling, so look in Power Query. I transformed your Date Range table from this:

edhans_1-1618505330492.png

to this:

edhans_2-1618505351700.png

 

then set the model up like this:

edhans_3-1618505378534.png

The DAX then becomes really easy.

 

My PBIX file is here

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Mike_12
Regular Visitor

Also, I am trying to do this without calculated columns.  

 

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.