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 EmployeeI 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
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
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
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