Forum Discussion
Difference between 2 columns in a Matrix Table
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
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
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
Hi Anonymous
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.
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.
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
Hi Anonymous
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
