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
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
Difference between 2 columns in a Matrix Table(1).zip73 KB

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: