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

Anonymous's avatar
Anonymous
Not applicable
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

Thanks a lot, v-shex-msft!

 

This is getting me on the right track.  Here's my DAX formula:  % & Diff =
var currentType=LASTNONBLANK('PeriodOrder'[Period], [Period])
return
SWITCH(currentType,"DELTA",SUM(BySupplier[NetSales])-SUM(BySupplier[GP]),FORMAT(DIVIDE(SUM(BySupplier[GP]), SUM(BySupplier[NetSales]),0),"percent"))

 

It returns the correct calculations for gross profit percent for 2016 and 2015 data (the data this report is looking at), but it still doesn't seem to affect the difference via "DELTA".  I'm trying different things but I keep getting results that seem to affect ALL gross profit percentages.

 

I think what it's doing is calculating the gross profit from "DELTA" in general and not looking at 2016 and 2015 data.  I need the difference to come from the previous two rows and not just calculate the percentage on the last row from the data in the prior columns of that same row.  Does that make sense?  Again, I think this is on the right track.

 

As always, I appreciate the help!

 

- Derek

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    HI jderekc,

     

    According to your description, you want to the GP% also works on total row, right?
    If this is a case, you can add a conditional to check the total row and write the total row formula in it.

     

     

    % & Diff =
    VAR currentType =
        LASTNONBLANK ( 'PeriodOrder'[Period], [Period] )
    RETURN
        IF (
            COUNTROWS ( BySupplier ) <> COUNTROWS ( ALL ( BySupplier ) ),//check total row
            SWITCH (
                currentType,
                "DELTA", SUM ( BySupplier[NetSales] ) - SUM ( BySupplier[GP] ),
                FORMAT (
                    DIVIDE ( SUM ( BySupplier[GP] ), SUM ( BySupplier[NetSales] ), 0 ),
                    "percent"
                )
            ),
            "TotalRow Formula"
        )

     

    Regards,

    Xiaoxin Sheng