Forum Discussion
Matrix - How to calculate percent difference between two different values of one column?
In the example of a matrix below, Term 1 and Term 2 are values from a variable called "Term" that I've placed in the Columns box. I'd like to add the "% difference" column as seen below, but I'm having trouble doing this. It seems like there would be an easy way to do this either with DAX or built-in options, but I'm unaware of the possibilities. Any help would be much appreciated. Thanks!
ROW: Campus
COLUMN: Term
VALUES: Count
| CAMPUS | Term 1 | Term 2 | % difference |
| Campus A | 100 | 110 | +10% |
| Campus B | 100 | 90 | -10% |
Hi, apagel
You can try the following methods.
Measure:
% difference = Var _N1=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),[Term]="Term 1"&&[Campus]=SELECTEDVALUE('Table'[Campus]))) Var _N2=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),[Term]="Term 2"&&[Campus]=SELECTEDVALUE('Table'[Campus]))) Return DIVIDE(_N2-_N1,_N1)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi apagel !
Try using SUMX Function!SUMX(
Table,
Table[Term1] - Table[Term2]
)
Let me know if that helps!
Cheers!
PabloDeheza Sorry, couldn't get it to work. Term 1 and Term 2 are not separate columns, but two different categories within one column. I tried adding the value (table[Term] = "Term 1"), but it didn't work.
Hi, apagel
You can try the following methods.
Measure:
% difference = Var _N1=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),[Term]="Term 1"&&[Campus]=SELECTEDVALUE('Table'[Campus]))) Var _N2=CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'),[Term]="Term 2"&&[Campus]=SELECTEDVALUE('Table'[Campus]))) Return DIVIDE(_N2-_N1,_N1)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
