Forum Discussion

apagel's avatar
apagel
Regular Visitor
4 years ago
Solved

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 havi...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

PabloDeheza's avatar
PabloDeheza
Solution Sage
4 years ago

Hi apagel !
Try using SUMX Function!

SUMX(

Table,

Table[Term1] - Table[Term2]

)

Let me know if that helps!

Cheers!

  • apagel's avatar
    apagel
    Regular Visitor
    4 years ago

    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.