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

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

      • Boycie92's avatar
        Boycie92
        Resolver I
        9 years ago

        Hi @Xiaoxin_Sheng

         

        You solution seems to be working. However I have another slight issue that I didn’t anticipate.

         

        The problem I have is that: In September there are employees in Department 1 but in October there are zero. (The Data is correct) However you’re MaxMonthHC shows last month’s (Septembers) value. Instead of a 0. Is there any way your measures could be changed to account for this?

         

        Thanks