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,

 

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

 

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

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