topic Re: Sum based on other column values (IF, then...) in DAX Commands and Tips

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

carlenb — Wed, 01 Nov 2023 09:38:18 GMT

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

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

some_bih — Wed, 01 Nov 2023 12:10:52 GMT

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?

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

carlenb — Wed, 01 Nov 2023 12:21:11 GMT

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

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

some_bih — Wed, 01 Nov 2023 15:32:30 GMT

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

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

carlenb — Thu, 02 Nov 2023 15:08:10 GMT

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

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

some_bih — Thu, 02 Nov 2023 15:23:26 GMT

Hi carlenb so for condition

In neither Quantity or Total value = 0 then sum Invoiced value othwerwise Total value?
Order number is key-id?

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

carlenb — Sat, 04 Nov 2023 09:35:44 GMT

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

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

carlenb — Sat, 04 Nov 2023 09:38:33 GMT

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

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

carlenb — Sun, 05 Nov 2023 09:33:56 GMT

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