Sumif from Excel in Dax (power bi)

[ReneKaibinger]

Hi, 

I am currently working on a report containing two tables: 

- Table 1 containing budget for job no and job task

- Table 2 containing actual for job no and job task 

Please note for both tables that there are mulitple lines for the same job no and task. In "result/summary table" i will summarize the values from table 1 and table 2. In Excel i have written following formula for cell L4 (budget) = SUMIFS(D:D;B:B;J:J;C:C;K:K). How do i write this formula in Power BI dax? Do i need to use measure or calculated colum or make a summarized table?

I have tried to make the relationship between the tables active, but without luck. I would like to sum based on columns and not "text". 

Thank you in advance.

[Greg_Deckler · Accepted solution]

@ReneKaibinger Excel to DAX Translation - Microsoft Power BI Community

[ReneKaibinger]

Thank you both 🙂

Thanks to your solutions i now works. 

[v-binbinyu-msft · Accepted solution]

Hi @ReneKaibinger ,

Please try below steps:

1. create two measure with below dax formula

Measure =
VAR cur_jn =
    SELECTEDVALUE ( Table1[Job No] )
VAR cur_jt =
    SELECTEDVALUE ( Table1[Job Task] )
VAR tmp =
    FILTER ( ALL ( Table1 ), Table1[Job No] = cur_jn && Table1[Job Task] = cur_jt )
RETURN
    IF (
        HASONEVALUE ( Table1[Job No] ),
        SUMX ( tmp, [Budget] ),
        SUMX ( ALL ( Table1 ), [Budget] )
    )

Measure2 =
VAR cur_jn =
    SELECTEDVALUE ( Table1[Job No] )
VAR cur_jt =
    SELECTEDVALUE ( Table1[Job Task] )
VAR tmp =
    FILTER ( ALL ( Table2 ), Table2[Job No] = cur_jn && Table2[Job Task] = cur_jt )
RETURN
    IF (
        HASONEVALUE ( Table1[Job No] ),
        SUMX ( tmp, [Actual] ),
        SUMX ( ALL ( Table2 ), [Actual] )
    )

2. add a table visual with Table1 fields and measure

Please refer the attached .pbix file.

 Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[freidav1984]

Hi ReneKaibinger,

Greg_Deckler's post is awesome! It is certainly a good material to use.

Based on your example, I created a calculated table. I tried to go step-by-step in order to show the solution on a transparent way. See the code below:

(There is certainly a better performing solution, but I guess it shows the logic of calculated tables and table variables)

RESULT = 

//Table variable for table 1
Var VarTable1 = 
    SELECTCOLUMNS(
        VALUES(
            'Table 1'
        ),
    "Job_No", [Job No],
    "Job_Task", [Job Task]
    )

//Table variable for table 2
Var VarTable2 =
    SELECTCOLUMNS(
        VALUES(
            'Table 2'
        ),
    "Job_No", [Job No],
    "Job_Task", [Job Task]
    )

//Union of two tables and creating distinct values of its column-pairs in case there would be different options in the 2 tables
Var VarTable1_2 = 
DISTINCT(UNION(VarTable1, VarTable2))

// Creating the final result table
Var VarTableResult = 
    ADDCOLUMNS(
        VarTable1_2,
    "Budget_fromTable1", CALCULATE(SUM('Table 1'[Budget]), FILTER('Table 1', 'Table 1'[Job No] = [Job_No] && 'Table 1'[Job Task] = [Job_Task])),
    "Actual_fromTable2", CALCULATE(SUM('Table 2'[Actual]), FILTER('Table 2', 'Table 2'[Job No] = [Job_No] && 'Table 2'[Job Task] = [Job_Task]))
    )

Return VarTableResult

The modell is shown below. (in this case no relations needed)

And the result:

Good luck!

David

[Greg_Deckler · Accepted solution]

@ReneKaibinger Excel to DAX Translation - Microsoft Power BI Community