Wrong subtraction results between two variables

[samoberoi]

Hi,

I am not getting the right subtraction results on subtracting the values derived from the two created variables based on some conditions. 

Please find the sample data with used measures and expected results here.

________________________________________________________________________________________________________________

TABLE B

Country	Tax Plan	Amount
England	BLL Type 2	159.46
England	BMM Type 5	2077.18
England	GL Type 1	52.52
England	GL Type 2	31685.68
England	IFTD Type 2	92
England	QTEL Type 2	878.86
England	QTEL Type 5	458.54
Wales	GL Type 1	-67
Wales	GL Type 2	2127.62
Wales	QTEL Type 2	8264.19

Table A 

Country	LType	Tax Plan	Amount
England	Type 1	GT Free Tax	-305
England	Type 2	GT Free Tax	350.62
England	Type 2	QT Free Tax	585.26
England	Type 5	QT Free Tax	137.56
England	Type 5	GT Free Tax	410.2
England	Type 5	QT Free Tax	2956.12
Wales	2013 boohoo	X Free G Tax	210
Wales	P-2013 boohoo	X Free G Tax	10
Wales	Type 2	QT Free Tax	104.56
Wales	Type 2	GT Free Tax	934.52
Wales	Type 2	QT Free Tax	1573

Measure used in Table B - Test = CALCULATE(

                                                           SUM('Table B'[Amount]),

                                                          FILTER(

                                                          'Table B',

                                                          'Table B'[Tax Plan] = "GL Type 1"

                                                           && 'Table B'[Country] in {"England","Wales"}))

Final results measure in Table A  - Subtraction_measure =

                                                                              VAR A =

                                                                                          CALCULATE(

                                                                                                    SUM('Table A'[Amount]),

                                                                                                               FILTER(

                                                                                                                      'Table A',

                                                                                                       ('Table A'[Country] = "England"

                                                                                                      && 'Table A'[LType] = "Type 2"

                                                                                                      && 'Table A'[Tax Plan] = "GT Free Tax")

                                                                                                      ||

                                                                                                     ('Table A'[Country] = "Wales"

                                                                                                    && 'Table A'[LType] = "Type 2"

                                                                                                   && 'Table A'[Tax Plan] = "QT Free Tax")))

                                                                                                    VAR B = [Test]

                                                                                                   RETURN

                                                                                                   B - A

Expected results looked for in the measure called "Subtraction_measure".

For Wales ----   1573+104.56 = 1677.56

                        -67-1677.56 = -1744.56

England ----    52.52 - 350.62 =  -298.10

______________________________________________________________________________________________________________

Apologies, i tried to load the PBI file link here, but it didn't work; so i had to used this method of giving you sample data and expected results. For any help on this, it will be much appreciated.

TIA

[Anonymous · Accepted solution]

Hi @samoberoi 

Thank you very much danextian for your prompt reply.

I did some testing and there doesn't seem to be anything wrong with your code. Running them individually gives the correct results, like this:

Do you want Subtraction_measure to display an equation? If so, you can use the following code:

Subtraction_measure = 
VAR A =
    CALCULATE(
        SUM('Table A'[Amount]),
        FILTER(
            'Table A',
            ('Table A'[Country] = "England"
            && 'Table A'[LType] = "Type 2"
            && 'Table A'[Tax Plan] = "GT Free Tax")
            ||
            ('Table A'[Country] = "Wales"
            && 'Table A'[LType] = "Type 2"
            && 'Table A'[Tax Plan] = "QT Free Tax")
        )
    )
VAR B = [Test]
VAR Result = B - A
RETURN
    FORMAT(B, "0.00") & " - " & FORMAT(A, "0.00") & " = " & FORMAT(Result, "0.00")

Here is the result.

If you still have questions, please provide your desired results. This is best presented in a table format.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samioberoi]

Hi v-nuoc-msft,

Thank you for your help. It worked to some extent and solved the problem.

[danextian]

Hi @samoberoi 

This part of the test measure instructs DAX to filter countries in Table B that are either England or Wales. However, it does not direct DAX to return "England" or "Wales" if the current row's country in Table B is either England or Wales, as this tuple isn't related to Table B at all. As a result, the total of both countries is shown for each row.  Repalce that with  'Table B'[Country] IN VALUES('Table A'[Country])

'Table B'[Country] IN { "England", "Wales" }

Test =
CALCULATE (
    SUM ( 'Table B'[Amount] ),
    FILTER (
        'Table B',
        'Table B'[Tax Plan] = "GL Type 1"
            && 'Table B'[Country] IN { "England", "Wales" }
    )
)

[samioberoi]

Hi, 

I tried to create the measure for table B as you advised; however, it is giving the same results as before and doesn't give any desired results, unless i am understanding something wrong.

Amended version of Table B - Test = CALCULATE(

                                                           SUM('Table B'[Amount]),

                                                          FILTER(

                                                          'Table B',

                                                          'Table B'[Tax Plan] = "GL Type 1"

                                                           && 'Table B'[Country] in values(Table A'[Country])

Let me show you the structure of my data model here. Sorry, i should have done it before.

Thanks for any help in advance.