Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate difference between 2 values that are a calculated measure

I have a calculated measure calls Sale Per Week and I have a matrix visual that has 2 columns for 2 different Brands.   I want to do a calculation to show that one Brands sales per week are how muc...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Got your message.  Thanks.  FYI that your model has a lot of bidirectional relationships and some many:many relationships.  That complicates the DAX in your measures.  Simple model, simple DAX.  I encourage you to simplify your model, if you plan to do further analyses.  In any case, here is a measure that gets your desired results.

     

    SALES DIFF PER WEEK = 
VAR __thisbrand = [SALES PER WEEK]
VAR __otherbrands = 
    SUMX(EXCEPT ( ALL ( productmaster[BRAND] ), VALUES(productmaster[BRAND]) ),
        [SALES PER WEEK])
RETURN
  __otherbrands - __thisbrand

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

I thought you had Brand in the visual.  The __otherbrands variable is returning blank.  You can add ALLSELECTED(Table) to address that I believe:

Difference =
VAR __thisbrand = [Sale per week]
VAR __otherbrands =
    CALCULATE (
        [Sale per week], ALLSELECTED(Table),
        EXCEPT ( ALL ( Table[Brand] ), VALUES ( Table[Brand] ) )
    )
RETURN
    __otherbrands - __thisbrand

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Pat,

 

Brand actually is in the visual as columns