Calculate value difference from column of a same table with hierarchy from another table

[feyd]

Hi everyone,

I have to calculate value difference between rows of the same column in one table where "File Name" column from "Database" table has "File for comparison" file from "DIM_File" table. I've tried with USERELATIONSHIP and it worked, but I was unable to filter the result by "Group".

What I'm trying to get -> Difference=sum(F2[Amount])-sum(A1[Amount])

or visually

Group	Prodcut Code	Amount F2	Amount A1	Difference
X	100	190	0	190
Y	200	50	130	80

Model looks like this

Database table sample:

File Name	Date	Product Code	Amount
A1	1.jan.2018.	200	50
A1	1.jan.2018.	100	0
A1	1.feb.2018.	200	80
F2	1.jan.2018.	200	50
F2	1.jan.2018.	100	100
F2	1.feb.2018.	200	0
F2	1.feb.2018.	100	90
B1	1.jan.2017.	200	110
B1	1.jan.2017.	100	100
B1	1.feb.2017.	200	80
B1	1.feb.2017.	100	90

DIM_File table sample:

File Name	Year	File for comparison	File type
A1	2018		A
F2	2018	A1	A
B1	2017		B

DIM_Product table sample:

Product Code	Group code	Name
200	1	Prod1
100	2	Prod2
300	1	Prod3

DIM_Group table sample:

Group code	Group
1	X
2	Y

Thanks!

[Greg_Deckler · Accepted solution]

I would look at the LOOKUPVALUE function in DAX. https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

[feyd]

Worked like a charm 🙂

Thanks!