Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculation of Differences

Hi everyone.   I have been trying to calculate the difference between the Basic Pay of each Employee for each Month, whereby the "Employee" field is the same. Refer to a sample of the data at hand ...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi Anonymous ,

    I modified the formula that you can have a try.

    Diff = 
var pay = SWITCH(TRUE(),Table1[Basic Pay] = BLANK(),0,Table1[Basic Pay])
var last = CALCULATE(SUM(Table1[Basic Pay]),FILTER(ALLEXCEPT(Table1,Table1[Employee]),Table1[Month] = EARLIER(Table1[Month])-1))
var mi = MIN(Table1[Month])
return 
IF(Table1[Month] = mi,0,pay - last)

    I have attached my sample. If it still doesn't work in your file, please point the difference between my sample and yours.

     

    Best Regards,

    Xue Ding

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks again for your reply.

 

I still haven't solved the problem tbh so unfortunately I can't accept it as a solution since the totals are very important. I tried adjusting the measure to include the zeros but I didn't manage.

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

 

Ok. Then explain clearly with an example what you need at the totals so that I can try to help

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks again for your help.

     

    Kindly find an example below:

    As you can see, the total is required so that I can sort the data from highest to lowest, depending on the total net differences for each employee.

     

    Let me know if you require further clarifications.

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

       

      So you need the total to sum over the months per employee? Try this:

      MoMDiff =
VAR CurrentMonthPay_ =
    SUM ( Table1[Basic Pay] )
VAR PreviousMonthPay_ =
    IF (
        SELECTEDVALUE ( Table1[Month] ) = 1,
        CurrentMonthPay_,
        CALCULATE (
            SUM ( Table1[Basic Pay] ),
            FILTER (
                ALL ( Table1[Month] ),
                Table1[Month]
                    = ( SELECTEDVALUE ( Table1[Month] - 1 ) )
            )
        )
    )
RETURN
    CurrentMonthPay_ - PreviousMonthPay_

       

      Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Unfortunately, your solution does not solve my problem. The total I would like to obtain is the total of the Differences.

         

        For example: In case of EMP1, given that his employee increased by €200 in month 2 and by €1000 in month 4, then the total difference for that employee is €1200 (as can be seen in the screenshot within my previous reply).

         

        For ease of reference, kindly find the table that I am currently using below:

         

        EmployeeMonthBasic PayMonthNo
        EMP10011,0001
        EMP20011,5001
        EMP30012,0001
        EMP10021,2002
        EMP20021,3002
        EMP30022,1002
        EMP10031,2003
        EMP20031,4003
        EMP30032,2003
        EMP10042,2004
        EMP20043,0004
        EMP30044,0004

         

        Apologies for not doing so earlier.

         

        Thanks once again!