Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to Solution.
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:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |