Forum Discussion

skylarw's avatar
skylarw
Frequent Visitor
8 years ago
Solved

Variance in Average Pay

Hey guys,

 

I'm new to Power BI and I have a hard time following the forums so if this question was already posted somewhere, I apologize.

 

I have a list of 1300 employee names along with their positions, pay dates and gross pay. I'm trying to show spikes (variance of %15+) in the average gross pay of each employee.

 

Since there are so many names, I need to only show what's important and in a clear way, maybe highlighting in red the employees with a variance of %15+.

 

Is there a simple way to do this? Because if it's code based, i'm lost.

 

 

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi skylarw,

     

    Try this measure please. You can check it out in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgSJ77V2S2Ik1Q7r6

     

    Measure =
VAR lastPaydate =
    CALCULATE (
        MAX ( 'Rows 1 to 1523'[Pay Date] ),
        FILTER (
            ALL ( 'Rows 1 to 1523' ),
            'Rows 1 to 1523'[Pay Date] < MAX ( 'Rows 1 to 1523'[Pay Date] )
        )
    )
VAR lastGrossPay =
    CALCULATE (
        SUM ( 'Rows 1 to 1523'[Gross Pay] ),
        'Rows 1 to 1523'[Pay Date] = lastPaydate
    )
RETURN
    DIVIDE ( SUM ( 'Rows 1 to 1523'[Gross Pay] ) - lastGrossPay, lastGrossPay, 0 )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

10 Replies

  • GabrielSantos's avatar
    GabrielSantos
    Resolver I
    8 years ago

    This can certainly be done, but I'm not sure if it can be done without writing a DAX formula (is this what you mean by code-based?).

     

    I'm happy to help, but a little more context on how you'd like to see your variance would be beneficial. Are you trying to show spikes of 15% in average gross pay of each employee as compared to other employees in their positions (i.e. Accountant 1 earns 20% more on average than all Accountants, therefore highlight) or show spikes relative to prior pay dates (i.e. Accountant 1 earned 25% more on his 9/5 paycheck than his 1/1 paycheck)?

     

    • skylarw's avatar
      skylarw
      Frequent Visitor
      8 years ago

      :smileyvery-happy: Yes that's what I mean!

       

      I'm trying to see spikes for each employee based on prior pay date as a way to keep track of any irregularities (such as an employee working way more hours than scheduled etc.)

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi skylarw,

         

        I think a formula is needed. But we can't create a formula without data. Could you please post a dummy data in text mode? (pbix file would be great. )

         

        Best Regards!

        Dale