need help with a measure

[Anonymous]

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

[v-juanli-msft]

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 · Accepted solution]

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.

---