Sum values from table A and B. But ignore data from table A when B has data in same period.

[Anonymous]

Good day,

I am struggling finding a solution to my problem. 

I have two tables. Table A and B. As per below. Table A is logged data from a SQL database, and Table B is manually inserted data to replace data in table A, whenever there is data logged with errors. 

Table A:

Date	TagNo	Value
01-04-2023	1	1000
02-04-2023	1	500
03-04-2023	1	600
05-05-2023	1	400
06-05-2023	1	500

Table B:

Date	TagNo	Value
01-04-2023	1	1100

I managed to use: 

Summed value = 

IF( SUM(Table B[Value]) > 0, SUM(Table B[Value]), 
    SUM (Table A [Value]) 
)

Using above measure, when selecting April, then total value is = 1100 (As per table B)

And when selecting May, total is = 900 (As per table A).

But when I select both April and May, then value is 1100, since the IF statement ignores data from table A.

Basically I need the measure to mainly sum values from table A, but when user manually inserts monthly corrected data in Table B, then values in Table A should be ignores. And I need it to sum value in the date range user has selected.

Thanks in advance!

Best,

Daniel 

[johnt75]

Try

Total =
SUMX (
    'Table A',
    VAR BValue =
        LOOKUPVALUE (
            'Table B'[Value],
            'Table B'[Date], 'Table A'[Date],
            'Table B'[Tag no], 'Table A'[Tag no]
        )
    RETURN
        COALESCE ( BValue, 'Table A'[Value] )
)

[Anonymous]

Hi johnt75,

Thank you for your fast response.

If I am mistaken your solution will sort of replace a value in Table A if it exists in Table B.

Thing is, that in table a I collect data loggings every 10 min. And data logged in table B, is the whole month summarized in one logging. So the data in Table B represent total for a month, and I want that to replace the many rows in sameperiod in Table A. Hope it makes sense.

So basically, I am not interested in replacing a single value, But I want to replace the summed total with the new total inserted in Table B.

Thanks.

[johnt75 · Accepted solution]

Assuming that you have a date table linked to both A and B, you can use

Total =
SUMX (
    VALUES ( 'Date'[Year month] ),
    COALESCE (
        CALCULATE ( SUM ( 'Table B'[Value] ) ),
        CALCULATE ( SUM ( 'Table A'[Value] ) )
    )
)

[Anonymous]

Hi again,

Worked perfectly - thanks a million 🙂

Have a great day ahead.

/Daniel