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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
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

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's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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