Forum Discussion
Need help on DAX function
Hi everyone I'm confused by the DAX function. I want to compare the budget and contract amount based on a table with the budget and contract info with the columns like BudgetID, Year, Budget Amount,...
NicoH Hi,
I am not sure I completely get your result, but you can use SUMX to iterate through the whole table and get the whole sum.
SUMX('Table', [budget amount])Please, if I get it wrong, share your copyable dataset and result, thank you.
Hi vojtechsima
Thanks for your tip.
Please find the sample data
| budgetID | Year | department | budgetAmount |
| 110 | 2021 | IT | 4900 |
| 110 | 2022 | IT | 4700 |
| 200 | 2021 | HR | 1000 |
| 200 | 2022 | HR | 2000 |
| budgetID | Year | contractID | contractAmount |
| 110 | 2021 | c1 | 100 |
| 110 | 2022 | c2 | 200 |
| 110 | 2021 | c3 | 300 |
| 110 | 2022 | c4 | 400 |
| 110 | 2023 | c1 | 30 |
| 200 | 2021 | HR-C1 | 100 |
| 200 | 2022 | HR-C1 | 200 |
| 200 | 2022 | HR-C2 | 500 |
THe expected result
| Year | Department | BudgetAmount | ContractAmount |
| 2021 | IT | 4900 | 400 |
| 2022 | IT | 4700 | 600 |
Sorry, here is the table structure.
Budget(budget id, year, department, budget amount)
Contract(budget id, year, contract id, contract amount)
ContracetAmountMeasure = CALCULATE( SUM(Contract[contractAmount]), USERELATIONSHIP(Budget[budgetID], Contract[budgetID]))Could you let me know the linked key between Budget and Contract table?
Hi, NicoH
There is a non-active relationship between BudgetID and BudgetID ( I can then call it in the measure I sent).
