Forum Discussion

jderekc's avatar
jderekc
Helper IV
9 years ago
Solved

Alternate Calculations in Matrix

Hi all,   I am curious if there is a way to accomplish what I am trying to do.  Please bear with me...  I have a matrix that has limited interactive options (search by vendor or product type).  Thi...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi jderekc,

     

    You can refer to below formula to use row label to switch calculate formula:

    % & Diff = 
var currentType=LASTNONBLANK('Sample'[Type],[Type])
return
SWITCH(currentType,"D",SUM('Sample'[Net Sales])-SUM('Sample'[Gross profit]),FORMAT(DIVIDE(SUM('Sample'[Gross profit]), SUM('Sample'[Net Sales]),0),"percent"))

     

    Source table:

     

    Notice: Type A,B,C calculate percent, Type D calculate diff.

     

     

    Regards,

    Xiaoxin Sheng

jderekc's avatar
jderekc
Helper IV
9 years ago

Hi smoupre!

 

Thank you for the comments.  I was playing around with that DAX function referenced in the thread you shared.  Unfortunately when I was using it, the expression evaluated for true for everything.  For instance: if I put a value of "400" if the expression is true, then all GP % values turn to "400", including "DELTA".  I think this might be because "DELTA" is a period like the other two (2016 and 2015).  I'm going to work with it more tomorrow to see if I can get it to work, but I think I may not have much luck because "DELTA" is exactly the same as 2016 or 2015.  It's a period assigned to a value (1--2016, 2--2015, 3--DELTA) and the SQL behind it all is doing the calculations for the differences.

 

So I am subtracting 2015 from 2016 values to get differences and that is written in SQL.  Maybe I should take that out of SQL and put it all in DAX?  The total row for the matrix sums up 2016 and 2015 but doesn't subtract them.  Perhaps I am missing something adundantly obvious.  It's a bit late now, so I apologize if I am missing something quite simple.

 

Regards,

Derek