Calculate difference between 2 values that are a calculated measure

[Anonymous]

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?

[mahoneypat]

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 - __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]

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 - __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]

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

[Anonymous]

@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

[Anonymous]

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

[mahoneypat · Accepted solution]

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]

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.

[Anonymous]

Pat,

Brand actually is in the visual as columns

[lbendlin]

@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 ?