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
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
Pat,
Here is the layout. Brands are blanked out.
- mahoneypat
Ok. The original should work w/o the ALLSELECTED() (take that back out). Perhaps there is something in [Sales per week] that is causing the conflict. Can you share that measure? Or try this pattern with a simple SUM() for example.
Regards,
Pat