Only sum rows a field (A) when other values of another field (B) is not equal to 0

[Vinh]

Hi everyone, I'm trying to solve the problem as I mentioned in the title but the result is still wrong

I want to sum 'Sale' values for each plant A, B, C when the 'Gross Sale' is !=0, I put the condition when Gross sale is not equal to 0 in the measure for  'Sale' but the result is not correct

This is the origin formula:

• sum(
  aggr(
  if(sum(Total<AA,BB,CC>[NN])<>0,
  
  SUM([NN])
  
  
  
  +
  (Sum([Qty]*ADJUSTMENT_FLAG))
  ,
  
  Sum({<[Product]={'123'}>}[ASD Qty])
  +
  (Sum([Qty]*ADJUSTMENT_FLAG))
  )
  
  ,BB,[BB Desc.],AA,[AA Desc.],[AA Location],[MONTH],CCC,[CC Desc.])
  
  )
•  

I have tried a few set analysis but it's not work:

• sum({<[Gross Sale]={"=[Gross Sale]<>0"}>}aggr(
  if(sum(Total<Plant,XX,YY>[CC])<>0,
  SUM(CC)
  +
  (Sum(AA*AAA)),
  Sum()
  +
  (Sum()
  )
  ,AA,BB,CC,DD))

• sum(if(([Gross Sale])<>0,aggr(
  if(sum(Total<Plant,XX,YY>[CC])<>0,
  SUM(CC)
  +
  (Sum(AA*AAA)),
  Sum()
  +
  (Sum()
  )
  ,AA,BB,CC,DD))
•  

For example (I can't use 'Flag' in the measure above), I have:

Plant	Sale	Gross Sale
A	6	0
A	5000	5,653,542
A	17	5,523,4634
B	3000	23,322,235
B	20	0
C	2000	0
C	200	75,454,745

[rubenmarin]

Hi in set analysis to set a "lower or higher that 0" you need to write the two zero's, as:

sum({<[Gross Sale]={"<0>0"}>}aggr(...

In your data sample =sum({<[Gross Sale]={"<0>0"}>} Sale) returns 8217

[Vinh]

thank you for your reply, I tried yours but the result is not change (not correct), I'm not sure if it is because of the 'aggr' part

[rubenmarin]

It could be, but I don't know what that aggr does. Sum() seems incorrect, I don't know if it's just a summary or you really have an empty sum.

To check the aggr you can create a table with AA,BB,CC and DD as dimensions and copy the expression inside the aggr as expression in the table and check the result.

Btw, the difference between ">0<0" and "<>0" in set analysis is that the second also excludes negative values, it works just as ">0".

[Vinh]

i made a table as your guidance but i don't understand the part "copy the expression inside the aggr as expression in the table", is it the if part like this, if you mean the 'if' part then it does have value when i created in the new table 

• if(sum(Total<Plant,XX,YY>[CC])<>0,
  SUM(CC)
  +
  (Sum(AA*AAA)),
  Sum()
  +
  (Sum()
  )

This is the complete measure in my dashboard, but it's not correct, the gross sale condition is not affect the result at all

• sum({<[Gross Sales Amount]={">0<0"}>}
  aggr(
  if(sum(Total<AA,BB,CC>[NN])<>0,
  
  SUM([NN])
  
  
  
  +
  (Sum([Qty]*ADJUSTMENT_FLAG))
  ,
  
  Sum({<[Product]={'123'}>}[ASD Qty])
  +
  (Sum([Qty]*ADJUSTMENT_FLAG))
  )
  
  ,BB,[BB Desc.],AA,[AA Desc.],[AA Location],[MONTH],CCC,[CC Desc.])
  
  )

[rubenmarin]

Yes, I meant to add the if inside the aggr, if doesn't works something is wrong with the expression. Try to split the if in different measures to check

- sum(Total<AA,BB,CC>[NN])

- SUM([NN])

...

You can also try to add the gross condition the each aggregation function

sum({<[Gross Sales Amount]={">0<0"}>}
aggr(
if(sum({<[Gross Sales Amount]={">0<0"}>} Total<AA,BB,CC>[NN])<>0
  ,SUM({<[Gross Sales Amount]={">0<0"}>}[NN])
    +(Sum({<[Gross Sales Amount]={">0<0"}>}[Qty]*ADJUSTMENT_FLAG))
  ,Sum({<[Product]={'123'},[Gross Sales Amount]={">0<0"}>}[ASD Qty])
    +(Sum({<[Gross Sales Amount]={">0<0"}>}[Qty]*ADJUSTMENT_FLAG))
)
,BB,[BB Desc.],AA,[AA Desc.],[AA Location],[MONTH],CCC,[CC Desc.])

[Vinh]

I did split it for checking and everything is normal, kinda strange when your first solution is not working

And for putting condition the each aggr func, this made the values of 'Sales' column turn into 0, I also tried using 'if' instead of 'set analysis' but it is the same.

[rubenmarin]

At his point I would need an app with sample data to make some test.

[Vinh]

I updated the post with sample data that included for the "sales' formula, please have a look at it. I still haven't found any solution yet