Forum Discussion
Calculate difference between 2 values that are a calculated measure
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
Microsoft EmployeeYou 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 - __thisbrand
If 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 ?
Hi Pat, I had thought that this solved my problem but then when I added the calculation it is not calculating the difference. It is giving me the same value of each Brands Sales per week but as a negative number.
- 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
Pat,
Brand actually is in the visual as columns
