Forum Discussion
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 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
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.
4 Replies
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.
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" } ) )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.
