Forum Discussion
Calculate value difference from column of a same table with hierarchy from another table
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!
I would look at the LOOKUPVALUE function in DAX. https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
2 Replies
- Greg_Deckler
Community Champion
I would look at the LOOKUPVALUE function in DAX. https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
- feydFrequent Visitor
Worked like a charm :)
Thanks!