Forum Discussion
carlenb
Advocate II
Advocate IISum based on other column values (IF, then...)
Hi,
I have the following data:
- Sales data with Quantity, Total Value and Invoiced value
Problem:
- I want to create a measure that sums based on the below conditions:
- If the value in column 'Quantity' or column 'Total value' is >0 then take the value from 'Invoiced value'
- If the value in column 'Quantity' or column 'Total value' is 0 then take the value from 'Total value'
- If the value in column 'Invoiced value' is 'Not invoiced' then take the value from 'Total value'
How can I create this measure?
Table with data where the correct sum in this example would be 287.
| Quantity | Total value | Invoiced value |
| 0 | 6 | 11 |
| 0 | 10 | 11 |
| 5 | 0 | 15 |
| 0 | 15 | 12 |
| 1 | 123 | 140 |
| 1 | 23 | 5 |
| 2 | 6 | 6 |
| 0 | 9 | 7 |
| 4 | 1 | 10 |
| 1 | 0 | 1 |
| 5 | 50 | 5 |
| 3 | 5 | 15 |
| 2 | 3 | Not invoiced |
| 4 | 5 | Not invoiced |
| 1 | 8 | Not invoiced |
| 0 | 9 | 15 |
| 2 | 6 | 14 |
| 4 | 1 | 12 |
| 5 | 0 | 11 |
| 0 | 15 | 11 |
8 Replies
carlenbHi some_bih
Thanks, well spotted! See below answers. You can also see the correct values for these scenarios in the screenshot in the thread start (Column E)
If the value in column 'Quantity' =0 and column 'Total value'>0 l(first row example) If Quantity = 0 then it would be Total value even if Total value = 0 or >0
If the value in column 'Quantity' >0 and column 'Total value'=0 l(third row example) If quantity > 0 then go for Total value even if Total value is = 0
- carlenb
Edited my latest post with a new table, since I need to consider also local currency.
