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 col...
carlenbAdvocate II
Advocate IIHi some_bih I checked and here are the conditions:
- If Quantity = 0 then sum Total value
- If Total value = 0 then sum Total value (which will be 0)
- If "Not invoiced" in Invoiced value then sum Total value
- In neither Quantity or Total value = 0 then sum Invoiced value
There is also one more requirement that popped up:
- If there are duplicate rows of order numbers, then pick only the MAX value for that order number
- See table updated with order number. So for example, for Order Number "5" the value would be 6 and not 6+6 since there are two rows with order number 5
| Quantity | Total value | Invoiced value | Order number |
| 0 | 6 | 11 | 1 |
| 0 | 10 | 11 | 2 |
| 5 | 0 | 15 | 3 |
| 0 | 15 | 12 | 3 |
| 1 | 123 | 140 | 4 |
| 1 | 23 | 6 | 5 |
| 2 | 6 | 6 | 5 |
| 0 | 9 | 7 | 6 |
| 4 | 1 | 10 | 7 |
| 1 | 0 | 1 | 8 |
| 5 | 50 | 5 | 9 |
| 3 | 5 | 15 | 10 |
| 2 | 3 | Not invoiced | 11 |
| 4 | 5 | Not invoiced | 11 |
| 1 | 8 | Not invoiced | 11 |
| 0 | 9 | 15 | 12 |
| 2 | 6 | 15 | 12 |
| 4 | 1 | 12 | 13 |
| 5 | 0 | 11 | 14 |
| 0 | 15 | 11 | 14 |
some_bih
Community Champion
Community ChampionHi carlenb so for condition
- In neither Quantity or Total value = 0 then sum Invoiced value othwerwise Total value?
- Order number is key-id?
- carlenb
Hi some_bih see below answers
- In neither Quantity or Total value = 0 then sum Invoiced value othwerwise Total value? Yes, and if the total value in column 'Invoiced value' is 'Not invoiced' then take the value from 'Total value'
- Order number is key-id? In this case yes. There are other columns such as [article number], [purchase order date] and [supplier id] but [order number] will be the key identifier for the sum
Edit:
One update since yesterday is that I have calculated Total Value in local currency with the below measure. This is something to consider too becuase instead of Total Value I need to use Invoiced value based on the conditions mentioned above. I have updated the table so that this is reflected. The reason for updating is because I realized the problem was more complex than first.
Total Value _ Exchange =SUMX('Sales 2019-2022','Sales 2019-2022'[Total Value] *LOOKUPVALUE('Exchange_currency'[Exchange rate],'Exchange_currency'[Date], 'Sales 2019-2022'[Purchase Order Date],'Exchange_currency'[Currency], 'Sales 2019-2022'[Crcy]) )Order number Quantity Total value Invoiced value Currency Purchase order date 1 1 6 11 SEK 2019-04-02 1 0 10 11 NOK 2019-04-03 2 5 0 15 EUR 2019-04-04 2 2 15 12 EUR 2019-04-05 3 1 123 140 EUR 2019-04-06 3 0 23 5 SEK 2019-04-07 4 2 6 6 USD 2020-04-02 4 1 9 7 NOK 2020-04-03 5 4 1 10 EUR 2020-04-04 5 1 0 1 EUR 2020-04-05 6 5 50 5 EUR 2020-04-06 6 0 5 15 SEK 2021-04-07 7 2 3 Not invoiced USD 2021-04-02 7 4 5 Not invoiced NOK 2021-04-03 8 1 8 Not invoiced EUR 2021-04-04 8 1 9 15 EUR 2021-04-05 9 2 6 14 EUR 2021-04-06 10 4 1 12 SEK 2022-04-07 11 5 0 11 USD 2022-04-08 11 1 15 11 SEK 2022-04-09