Forum Discussion

NicoH's avatar
NicoH
Regular Visitor
4 years ago

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,...
Need Help
vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
4 years ago

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's avatar
    NicoH
    Regular Visitor
    4 years ago

    Hi vojtechsima

    Thanks for your tip.

    Please find the sample data

    budgetIDYeardepartmentbudgetAmount
    1102021IT4900
    1102022IT4700
    2002021HR1000
    2002022HR2000

     

    budgetIDYearcontractIDcontractAmount
    1102021c1100
    1102022c2200
    1102021c3300
    1102022c4400
    1102023c130
    2002021HR-C1100
    2002022HR-C1200
    2002022HR-C2500

     

     

    THe expected result

    YearDepartmentBudgetAmountContractAmount
    2021IT4900400
    2022IT4700600
    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, NicoH 

       

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

       

       

      • NicoH's avatar
        NicoH
        Regular Visitor
        4 years ago

        Sorry, here is the table structure.

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

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

         

    • vojtechsima's avatar
      vojtechsima
      Icon for Super User rankSuper User
      4 years ago

      NicoH 

      ContracetAmountMeasure = 
CALCULATE(
    SUM(Contract[contractAmount]), USERELATIONSHIP(Budget[budgetID], Contract[budgetID]))
      • NicoH's avatar
        NicoH
        Regular Visitor
        4 years ago

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