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

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?