Forum Discussion

samoberoi's avatar
samoberoi
Icon for Helper III rankHelper III
1 year ago
Solved

Wrong subtraction results between two variables

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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's avatar
    samioberoi
    Icon for Helper III rankHelper III
    1 year ago

    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.