cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Employee

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

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.
10 REPLIES 10
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)?

Frequent Visitor

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

Employee

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

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?

Employee

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

Okay, awesome!

Sorry not sure how to get it to pbix format.

Super User

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
Employee

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

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

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

Employee

Hi @skylarw,

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.

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors