cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Combine two tables (consolidate on multiple criteria) & Add new rows

Dear Power BI enthusiasts,

 

these days I did struggle with the combination of two datasets. I want to Sum in tab_1 the values of tab_2 that matches the two criteria columns. With LookUpValue I failed since in tab_2 are multiple rows per criteria set. A SumX I did only manage when hardcoding the criteria = "123" = "abc" as String, but I want to refer the formula to the specific row it is in. Can anyone help me with a SumIF like formula to bring the Value from tab_2 into tab_1.

 

tab_2

Crit 1Crit 2ValTab_2
123abc1,01
123abc0,56
123def0,40
456def0,15
456abc3,52
456ghi13,93
789ghi14,09
789def0,50
789ghi0,19
123xyz0,67
789xyz0,67

 

tab_1

Crit 1Crit 2ValTab_1ValTab_2 (result expected)
123abc3,041,58
123def1,200,40
456def0,460,15
456abc10,553,52
456ghi41,7813,93
789ghi42,2814,29
789def0,060,50
123xyz 0,67
789xyz 0,67

 

This would already help me to get a viable entry into the world of formulas.

 

But there is also an addition: How to manage, that, if in tab_2 are rows that do not have a corresponding 'target' in tab_1 to consolidate them to via a SumIF. Could those rows be attached to tab_1? But only the rows that do not have a match with SumIf ! I tried this via the 'append' function, but there ALL rows have been added.

(The bold rows from tab_1 are the result. In tab_2 thesd two are italic.)

 

Is Power BI Desktop capable of doing this?

 

Many many thanks for your thoughts on a) My SumIf Problem and b) the "attach rows without a target" problem.

 

Best regards, Andreas

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Combine two tables (consolidate on multiple criteria) & Add new rows

@Andreas_H

 

Please see attached file here with your sample data

 

combine.png

Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Combine two tables (consolidate on multiple criteria) & Add new rows

Hi @Andreas_H

 

Try this calculated table

 

from the modelling tab>>>NEW TABLE

 

New tab_1 =
VAR missingrows =
    ADDCOLUMNS (
        EXCEPT (
            ALL ( tab_2[Crit 1], tab_2[Crit 2] ),
            ALL ( tab_1[Crit 1], tab_1[Crit 2] )
        ),
        "ValTab_1", BLANK ()
    )
RETURN
    ADDCOLUMNS (
        UNION ( tab_1, missingrows ),
        "valtab_2", CALCULATE (
            SUM ( tab_2[ValTab_2] ),
            FILTER (
                tab_2,
                tab_2[Crit 1] = EARLIER ( [Crit 1] )
                    && tab_2[Crit 2] = EARLIER ( [Crit 2] )
            )
        )
    )
Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

Re: Combine two tables (consolidate on multiple criteria) & Add new rows

@Andreas_H

 

Please see attached file here with your sample data

 

combine.png

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Frequent Visitor

Re: Combine two tables (consolidate on multiple criteria) & Add new rows

Dear Muhammad,

 

I can hardly believe! That is awesome! I still don't get it... with VBA I would take me hours and hours to write such a script and you returned a result in some minutes only. My sincere respect goes to you!

I need to further look into your approach and try to understand the syntax. And I need to learn DAX! The possibilities seem to be endless.

 

Many, many thanks to you!

 

Have a great weekend! Best wishes, Andreas

 

PS: I still can not believe... Wow...

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors