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 ...
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

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

      • skylarw's avatar
        skylarw
        Frequent Visitor
        8 years ago

        Thanks v-jiascu-msft but I'm not sure how I can attach a file.

         

        I also only have an xlsx file- I'm not sure how to convert it.

         

        Any ideas?