Sum based on other column values (IF, then...)

[carlenb]

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

[carlenb · Accepted solution]

I managed to solve this myself. Many thanks @some_bih for helping along the way and asking the right questions! 

[carlenb]

Edited my latest post with a new table, since I need to consider also local currency. 

[some_bih]

Hi @carlenb there is possible scenario 

If the value in column 'Quantity' =0 and column 'Total value'>0 l(first row example)
If the value in column 'Quantity' >0 and column 'Total value'=0 l(third row example)

what to do?

[carlenb]

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

[some_bih]

Hi @carlenb could you please once again check your conditions =0 and >0 could be Invoiced value and total value

[carlenb]

Hi @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]

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