Forum Discussion

Boycie92's avatar
Boycie92
Resolver I
9 years ago
Solved

Difference between 2 columns in a Matrix Table

Hi all,

 

I was wondering if someone could help me.

 

I want to calculate the difference between two columns on a Matrix Table

At row Level I have Department.

At Column Level I have Date. This is an actual Date column within the Data

At Values Level I have headcount. This is a measure and the following formula has been used: DISTINCTCOUNT(‘Employee’[Person ID])

I essentially want to compare the difference in Headcount between two specific dates.

 

For Example:

 

April                       May                       Difference

Headcount          Headcount          Headcount

10                           9                              -1

 

I have read other blog post and I have tried the following formulas

 

Previous Month = CALCULATE([HEADCOUNT],FILTER(ALL(‘Employee’[Date].[Month]),MAX(‘Employee’[Date])-1))

Difference = IF(ISBLANK([Previous Month]=0),CALCULATE([Headcount]-[Previous Month]))

 

By the looks of the results I am getting Mays Data.

 

When using the difference formula I am getting blank spaces

 

Can anyone tells me were in going wrong with this?

 

Thanks

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Boycie92,

     

    It seems like I forget some conditions. You can take a look at below formulas if it works on your side, I upload the pbix file.

     

    MinMonthHC =
    var temp= LASTNONBLANK(Sheet3[Department],Sheet3[Department])
    var resultMax= SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MaxMonth]),Sheet3[Headcount])
    var resultMin=SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MinMonth]),Sheet3[Headcount])
    var currMinCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MinMonth])))
    var currMaxCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MaxMonth])))
    return
    if(AND([minRowCount]=[maxRowCount],[maxRowCount]=COUNTAX(VALUES(Sheet3[Department]),[Department]))||AND(currMinCount=currMaxCount,currMaxCount=1),resultMin,if(currMinCount=0,0,resultMin))

     

    MaxMonthHC =
    var temp= LASTNONBLANK(Sheet3[Department],Sheet3[Department])
    var resultMax= SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MaxMonth]),Sheet3[Headcount])
    var resultMin=SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MinMonth]),Sheet3[Headcount])
    var currMinCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MinMonth])))
    var currMaxCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MaxMonth])))
    return
    if(AND([minRowCount]=[maxRowCount],[maxRowCount]=COUNTAX(VALUES(Sheet3[Department]),[Department]))||AND(currMinCount=currMaxCount,currMaxCount=1),resultMax,if(currMaxCount=0,0,resultMax))

     

    In addition, if above still contain some incorrect part, please provide sample data to test.(my test data is created by random function)

     

    Regards,

    Xiaoxin Sheng

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Boycie92,

     

    According to your description, you want to  get the diff between specify months, right?

     

    If as I said, you can refer to below measure:

     

    Diff =
    CALCULATE(SUM(Sheet3[Headcount]),FILTER(ALLSELECTED(Sheet3),Sheet3[Month]= MAX(Sheet3[Month])),VALUES(Sheet3[Department]))
    - CALCULATE(SUM(Sheet3[Headcount]),FILTER(ALLSELECTED(Sheet3),Sheet3[Month]= MIN(Sheet3[Month])),VALUES(Sheet3[Department]))

     

    Sample.

    Table:

     

    Month is a calculate which use to get the monthNO.

    Month = [Date].[MonthNo]

     

    Measures:

    MinMonth = MINX(ALLSELECTED(Sheet3[Month]),[Month])

    MaxMonth = MAXX(ALLSELECTED(Sheet3[Month]),[Month])

     

    Create visuals:

    Matrix:

     

    Slicer:

     

     

    Result:

     

     

    Regards,

    Xiaoxin Sheng

    • Boycie92's avatar
      Boycie92
      Resolver I
      9 years ago

      Hi Anonymous

       

      Thanks for getting back to me. Your solution works in part. However I have a couple of issues.

       

      Is there any way for the formulas to work with a Headcount measure? I need a distinct count of person ID. When I try this calculation as a column I get incredibly high/incorrect numbers. I need the Headcount formula to account for employees with multiple positions within the same department or a different one.

       

      Ideally I would like my matrix Chart to look like the bottom example. But instead of the 0 figures the actual difference. Is this possible?

       

      Thanks again for the Help!

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Boycie92,


        Based on test, my measure seems have this issue, but I haven't find a solution to solve it. For your requirement, I added two measure get the specify month's head count, you can try it if it works on your side:

         

        MinMonthHC =
        var temp= LASTNONBLANK(Sheet3[Department],Sheet3[Department])
        return
        SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MinMonth]),Sheet3[Headcount])

         

        MaxMonthHC =
        var temp= LASTNONBLANK(Sheet3[Department],Sheet3[Department])
        return
        SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MaxMonth]),Sheet3[Headcount])

         

        Visual:

         

        Regards,

        Xiaoxin Sheng