Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
achinchilla
Frequent Visitor

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.  TReg issues.jpg

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!!!
1 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 @achinchilla 

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

2.png

 

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.


 

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @achinchilla 

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

2.png

 

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.

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

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

2.png

 

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.


 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.