Difference between 2 columns in a Matrix Table

[Boycie92]

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

[v-shex-msft]

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]

Hi @v-shex-msft

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!

 

[v-shex-msft]

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]

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

[v-shex-msft]

Hi @Boycie92,

I have fixed the issue.

Measures:

maxRowCount = COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),[Date].[MonthNo]=[MaxMonth]))

minRowCount = COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),[Date].[MonthNo]=[MinMonth]))

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])
return
if(AND(resultMin=resultMax,[maxRowCount]>[minRowCount]),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])
return
if(AND(resultMin=resultMax,[maxRowCount]<[minRowCount]),0,resultMax)

Diff = [MaxMonthHC]-[MinMonthHC]

Result:

Notice: I uploaded the pbix file.

Regards,

Xiaoxin Sheng

[Boycie92]

Hi @v-shex-msft

I have used your measures and I seem to be getting a few incorrect results.

For instance:

In September, Department 1 had a headcount of 15. In October the headcount it is 0. You solution has reversed this. So the 0 is in September and the 15 is in October. 

Another error I am getting is that Department 2 had 20 employee in each month. (September and October). However like the above issue I get a 0 in September and 20 in October.

I was also wondering if there was a way for my total row in the matrix table to display the correct totals? At the moment it is just showing the totals for the last department.

I should mention that my data isn’t aggregated like yours. I have a whole list of individual records and each record has a date attached to it. Is this the reason for the issues I am having?

Thanks again for the help.

[v-shex-msft]

Hi @Boycie92,

You can modify the measures to fix this issue.(I add the condition to check the same value)

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 currectDeptRecordsCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MinMonth]||[Date].[MonthNo]=[MaxMonth])))
return
if(AND(resultMin=resultMax&&currectDeptRecordsCount<>2,[maxRowCount]>[minRowCount]),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 currectDeptRecordsCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MinMonth]||[Date].[MonthNo]=[MaxMonth])))
return
if(AND(resultMin=resultMax&&currectDeptRecordsCount<>2,[maxRowCount]<[minRowCount]),0,resultMax)

>>I was also wondering if there was a way for my total row in the matrix table to display the correct totals? At the moment it is just showing the totals for the last department.

Sorry, I haven't find the way to show the diff at matrix table, perhaps you can refer to Vvelarde's solution.

Regards,

Xiaoxin Sheng

[Boycie92]

Hi @v-shex-msft

I have used the measures you provided and it has had no change. I am still getting the same result.

For Department 1.  88 should be in MinMonthHC and 0 should be in MaxMonthHC. The diffence should be -88.  In addition Department 9 should have 15 in MinMonthHC and 15 in MaxMonthHC. The difference should be 0.

I am confused as to why it works on your end and not mine? 

Thanks

[v-shex-msft · Accepted solution]

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

[Boycie92]

Hi @Xiaoxin_Sheng

Your alterations work. However as soon as I use my date column as a slicer only 0 values appear for MinMonthHC.

I have attached a sample so you can see the data I am using and the issue I am experiencing.

I was unable to open you attachment as I’m running an older version of Power BI at the moment.

I appreciate your help on this. I never expected it to be so complex!

Thanks, 

[Boycie92]

Hi @v-shex-msft and @Vvelarde

In addition I am trying to update my charts to use a Hierarchy.

So it would go from Department to Team. I have noticed when I am using the Hierarch or individual structure levels in my table I keep on getting the wrong totals.

I have used this measure to sum the values instead of getting the values form the last department.

MinMonth = SUMX(VALUES('sheet 3'[Department]),[MinMonth 1])

MaxMonth = SUMX(VALUES('sheet 3'[Department]),[MaxMonth 1])

Diff = SUMX(VALUES('sheet 3'[Department]),[Diff 1])

For instance in one of the departments when it is selected as a Max month, it has 11 as the Number of positions. However when I select it as Min Month the total says 11 but manually adding the data the total should be 12. After checking the data I know that there should be 12 positions.

I think this is because a new team and has been added that wasn’t in the data before? Is there any way of fixing this? 

Thanks,

[Vvelarde]

@Boycie92

I attach a way to try to do it this:

Work with 2 calendars

-A measure to filter in the matrix the dates selected

- A measure to calculate the diff

Maybe is a little bit closer to find a solution.

Sample File