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.

AlB's avatar
AlB
Community Champion
7 years ago

 

yes, that's because of the SELECTEDVALUE(). At the Total there is no filter context and SELECTEDVALUE returns blank. Check it out here Please mark the response as solution if that's the case and consider kudoing if the posts are of help.

Cheers 

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