Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
I have tried a few set analysis but it's not work:
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 |
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
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
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".
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
This is the complete measure in my dashboard, but it's not correct, the gross sale condition is not affect the result at all
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.])
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.
At his point I would need an app with sample data to make some test.
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