Forum Discussion
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 much different than the other brand.
Any ideas?
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 - __thisbrandIf this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
13 Replies
- mahoneypat
Microsoft Employee
You can use this pattern to calculate the difference from the current brand from all the other brands:
Difference = VAR __thisbrand = [Sale per week] VAR __otherbrands = CALCULATE ( [Sale per week], EXCEPT ( ALL ( Table[Brand] ), VALUES ( Table[Brand] ) ) ) RETURN __otherbrands - __thisbrandIf this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- lbendlin
Super User
mahoneypat I don't understand - where does the EXCEPT exclude __thisbrand? Is that a lineage thing?
- lbendlin
or is Values() a trick to get the thisbrand value ?
- mahoneypat
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 - __thisbrandIf this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
