Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

need help with a measure

Hi community. I'm working in a project of Price Mix Analysis. I have a table with several columns of products and sub-products. I also have two columns with amounts (Sales and Budget). I need to compare the list of products between Sales and Budget and sum the values of Sales only if the budget column has a value for the product; the sub-product is not to be taken into consideration.  So, I created a measure to compare the 2 columns.  The output is 1 if the 2 columns have a value, if a value is missing in either column, the end value is 0.  

the formula for the column TReg is 

T Reg = CALCULATE(IF(OR([fActuals]=0, [fBudget]=0),0,1)).  
What I need to have is the TReg being calculated to the level of the Product (Finest); I don't care about the sub-products.  But the TReg column takes the last value of the Sub-Product and makes it 0 because there is a value 0 in the 3rd column.  My problem is that I want the 1 and 0 be calculated for the product, not the sub-product.  In the example above, the fifth column should add the same as the second column, but the 1's and 0's is taking one of the sub-products and is not summing the last value ($3).  I have tried allexcept, allselected, and others but they havent' worked.  Any help here?  Thanks!!!
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Maggie,

     

    This is great!  Thanks, however, this resolves part of it.  How do I ensure that the Overall Total sums up only the 15 of Product a since Product b is Zero?

     

    I appreciate your help, really!!!

     

    Albin 

    v-juanli-msft wrote:

    Hi Anonymous 

    Create a measure

    Measure 2 = IF(OR(CALCULATE(SUM(Sheet7[sales]),ALLEXCEPT(Sheet7,Sheet7[product]))=0,CALCULATE(SUM(Sheet7[budget]),ALLEXCEPT(Sheet7,Sheet7[product]))=0),0,SUM(Sheet7[sales]))

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Create a measure

    Measure 2 = IF(OR(CALCULATE(SUM(Sheet7[sales]),ALLEXCEPT(Sheet7,Sheet7[product]))=0,CALCULATE(SUM(Sheet7[budget]),ALLEXCEPT(Sheet7,Sheet7[product]))=0),0,SUM(Sheet7[sales]))

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie,

       

      This is great!  Thanks, however, this resolves part of it.  How do I ensure that the Overall Total sums up only the 15 of Product a since Product b is Zero?

       

      I appreciate your help, really!!!

       

      Albin 

      v-juanli-msft wrote:

      Hi Anonymous 

      Create a measure

      Measure 2 = IF(OR(CALCULATE(SUM(Sheet7[sales]),ALLEXCEPT(Sheet7,Sheet7[product]))=0,CALCULATE(SUM(Sheet7[budget]),ALLEXCEPT(Sheet7,Sheet7[product]))=0),0,SUM(Sheet7[sales]))

       

      Best Regards

      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.