Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
skylarw
Frequent Visitor

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.

 

 

 

 

1 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 )

Variance in Average Pay.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
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

10 REPLIES 10
GabrielSantos
Resolver I
Resolver I

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)?

 

Smiley Very 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.)

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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])

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 )

Variance in Average Pay.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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!

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?

Variance in Average Pay .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors