Need help on DAX function

[NicoH]

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?

[NicoH]

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.

@voji

[v-janeyg-msft]

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 _ Janey

[NicoH]

Hi Janey

Your solution works for the budget amount, but the contract amount does not work

[vojtechsima]

@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.

[NicoH]

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

[vojtechsima]

@NicoH 

ContracetAmountMeasure = 
CALCULATE(
    SUM(Contract[contractAmount]), USERELATIONSHIP(Budget[budgetID], Contract[budgetID]))

[NicoH]

Could you let me know the linked key between Budget and Contract table?

[vojtechsima]

Hi, @NicoH 
There is a non-active relationship between BudgetID and BudgetID ( I can then call it in the measure I sent).

[NicoH]

But it's a many-to-many link.

[vojtechsima]

@NicoH 
Well, yeah, it's definitely not Ideal, but it did achieve what you wanted.

[NicoH]

I tried but the result is not correct.

[vojtechsima]

Hi, @NicoH 
Did you use my measure to display the values in the table?

[NicoH]

no

[v-janeyg-msft]

Hi, @NicoH 

Are you sure that the columns on the dimension table are all placed in the table?

[NicoH]

Sorry, here is the table structure.

Budget(budget id, year, department, budget amount)

Contract(budget id, year, contract id, contract amount)

[v-janeyg-msft]

@NicoH 

I mean, are you using the columns from the dimension table in visual? 

[NicoH]

Yes, I tried and it can calculate the budget amount correctly, but the contract amount did not.

Now I'm using the composite key for 2 tables: budgetid_year in budget table, budgetid_year_contractid in contract table. 

[vojtechsima]

Hi, @NicoH 
did you check my newest post on this article?