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