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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

mahoneypat I removed the ALLSELECTED() and and I am not sure how I can create a simple SUM(). I tried to do a new measure for SUM(Table[Brand]) and it said Measure could not be found.

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

I didn't mean to sum the Brand column (error), but using a different measure in the first part would be a way to troubleshoot if it is the measure or the proposed approach.  That approach is working on my end, so there is something about your model/visual I am missing.  Can you share a pbix file?  Or mock data table(s)?

 

Regards,

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi mahoneypat I sent you a private message with link to download. Thank you.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you thank you mahoneypat . That did the trick! Were you able to figure out why I am getting 431 stores when it should be 2,182 or some number close to that.