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 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
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
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,
Hi Anonymous 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,