Variance in Average Pay

[skylarw]

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.

[GabrielSantos]

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]

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]

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]

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?

[v-jiascu-msft]

Hi @skylarw,

You can share the file from a cloud drive like OneDrive, Google Drive, Dropbox etc. And then paste the link here.

Best Regards!

Dale

[skylarw]

Okay, awesome!

@v-jiascu-msft Here's the link https://drive.google.com/open?id=0B1TLjBczsoBzdWtfb0VvZ2xLMDQ

Sorry not sure how to get it to pbix format.

[Ashish_Mathur]

Hi @skylarw,

My approach is quite similar to that of @v-jiascu-msft.  Since wages are paid fortnightly, here is my calculated field formula for computing the previous fortnight's wages

=CALCULATE([Total pay],DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-14,MIN('Calendar'[Date])-14))

Total Pay is

=SUM('Rows 1 to 1523'[Gross Pay])

[v-jiascu-msft · Accepted solution]

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]

@v-jiascu-msft Thank you so much! 

Would you be able to break it down a little in case I ever wanted to tweak it? 

Also, where do you define what percentage to highlight and what color to highlight in?

Is there also a way to only show the red highlighted data? Are there other ways to show it than in a matrix?

Thank you so much it's really beautiful!

[v-jiascu-msft]

Hi @skylarw,

Did you download the file? You can try it yourself. 

We can define it in the "Conditional formatting".

If we change the green color into white, there will be only red. But it would be a little ugly. If we format the font color, that would be perfect.

How about the table visual?

Best Regards!

Dale