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, ContractID, and Contract Amount.
Now the result like
If removed SUM on budget amount,
I think it sums up the budget amount on different contracts.
So How to sum up the budget amount without the contract ID?
18 Replies
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
Hi I tried your suggestion but no luck.
I have 2 tables: Budget and Contract.
Budget Table
budget id | Year | budget amount
110 | 2021 | 4900
110 | 2022 | 4710.4
Contract Table
BudgetID | contract ID | Year | contract amount
110 | c1 | 2021| 100
110 | c2 | 2021| 200
110 | c3 | 2022| 300
110 | c4 | 2022| 400
The result I want likes:
Budget ID | Year | Budget Amount | Contract Amount
110 | 2021| 4900 | 300
110 | 2022| 4710.4 | 700
Thanks in advance.
Hi, NicoH
I checked your model, your two tables have many-to-many relationships, what you need is to establish a master-slave table to improve the data model.
You can use distinct function to create single tables as slave table. According to the information you provide, you need to set a one-to-many relationship between budget id and year.
Like this:
Table = DISTINCT('Budget Table'[budget id])Table 2 = DISTINCT('Budget Table'[Year])Then you need to use slave table budget id and year and master columns in visual.
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ JaneyHi Janey
Your solution works for the budget amount, but the contract amount does not work
