Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 muc...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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