Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Benedictmooray
Frequent Visitor

Wrong subtraction results from two measures

Hi,
I am getting the wrong results on subtracting the two measures based on some conditions from Table A and Table B.

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

1 ACCEPTED SOLUTION
v-yajiewan-msft
Community Support
Community Support

Hi @Benedictmooray , 

 

Please check the following measure:

Measure_Difference1 =
VAR TotalA =
    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 TotalB =
    CALCULATE(
        SUM('Table B'[Amount]),
        FILTER(
            'Table B',
            'Table B'[Tax Plan] = "GL Type 1"
            && 'Table B'[Country] = SELECTEDVALUE('Table A'[Country])
        )
    )
RETURN
    TotalB - TotalA

Create another table visual to check the result:

vyajiewanmsft_0-1736919932149.png

Best regards,

Joyce

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

 

View solution in original post

1 REPLY 1
v-yajiewan-msft
Community Support
Community Support

Hi @Benedictmooray , 

 

Please check the following measure:

Measure_Difference1 =
VAR TotalA =
    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 TotalB =
    CALCULATE(
        SUM('Table B'[Amount]),
        FILTER(
            'Table B',
            'Table B'[Tax Plan] = "GL Type 1"
            && 'Table B'[Country] = SELECTEDVALUE('Table A'[Country])
        )
    )
RETURN
    TotalB - TotalA

Create another table visual to check the result:

vyajiewanmsft_0-1736919932149.png

Best regards,

Joyce

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.