cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blytonpereira Regular Visitor
Regular Visitor

Summarize over columns from Different Tables

Hello

 

I would like to use the SUMX function with Summarize over columns from different tables.

 

I have a table named "Region" with a single column also named "Region".

I have a second table named "P2" and  a single column named "P2".

 

For summarize in powerbi we are only allowed to use one table. Is there any way to tweak the equation to make it work for two tables like below ?

 

The measure should be SOMETHING LIKE

Equation= SUMX(SUMMARIZE(Table1(Column1),Table2(Column2)),MYMEASURE
4 REPLIES 4
AkhilAshok Established Member
Established Member

Re: Summarize over columns from Different Tables

Does these 2 tables, Region and P2, relate to a common fact table? If yes, then you have to SUMMARIZE over the common fact table to which these 2 tables are linked to:

 

Measure =
SUMX ( SUMMARIZE ( Fact, Region[Region], P2[P2] ), [Measure] )
Community Support Team
Community Support Team

Re: Summarize over columns from Different Tables

HI @blytonpereira,

 

It will be help if you provide some details information.

 

In my opinion, I'd like to suggest you create a variable table to add new column to expression table to store value from another table based on relationship.  Then you can simply to summarize and calculate on variable table.

 

For example:

Measure =
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS ( Table1, "T2Col", RELATED ( Table2[Column2] ) ),
        Table[Column],
        [T2Col]
    )
RETURN
    SUMX ( summary, [Measure2] )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
blytonpereira Regular Visitor
Regular Visitor

Re: Summarize over columns from Different Tables

@v-shex-msft

 

My raw original tables look like below

 

Table name:Black                                                            Table Name: White

P2     Region     Country       Value                                  Plan     Regionx   Countryx    Result

23x   ASPAC     Nord            2                                        7x         NA           JAP               2  

23x   EMEA       EAST            3                                        15x       NA           JAP               2  

18x   ASPAC     Nord            2                                        11x       NA           JAP               2  

17x   NA           JAP               2                                        11x       NA           JAP               2  

 

In my design I have used PowerQuery to

1. Create new table view and Group Table Black by P2 and Region

2. Create another new table view and Group Table White by P2 and Region

3. Append both 1. and 2 in another new table, this will be my brideg table connecting my raw data Black and White

4. Remove duplicates from 3,. All of these steps effecively will create a full outer join which is what I need.

5. Add a Third column and concatanate columns 1 and 2 to create a unique key

6. Create a one to many reationship from this unique key in this Bridge table to my orignal Black and White tables. (I also had to create the concatanated column in my original Black and white tables to make this link)

7. Now I  can write a Summarize DAX measure over the P2 and Region columns in the bridge table.

8. Lastly create another table of unique P2 from an APPEND of table Black and White. This is linked by a one to many relationship with P2 in my Black table and Plan in my white table.

 

As can be seen this is very heavy on the CPU processor since there are about 10 million rows.

 

I would like to understand if your way is infact simpler ? It does seem very light as you are only using a DAX function to summarize across 2 tables rather than create this bridge table which I have done ?

If your is indeed much lighter can you pls rewrite your DAX measure to match the column and table naming I have provided?

 

Why I made the bridge table ?

I wanted to create the following measure

ABS Error P2/Country = SUMX(SUMMARIZE('Bridge_table','Black'[P2_Region],'White'[PLan_Regionx]),ABS([Sum of Error]))

 Then in my table visualization I would like to display the following

 

P2(this is the result of Step 😎             Value (from Black table)              Result (from White)       ABS Error (measure above)

 

 

 

Community Support Team
Community Support Team

Re: Summarize over columns from Different Tables

Hi @blytonpereira,

 

>>If your is indeed much lighter can you pls rewrite your DAX measure to match the column and table naming I have provided?

Nope, they also need to be calculated in variable tables.

 

I'd like to suggest you create bridge table on date view side.

 

Steps:

1. Add calculated column PR to raw tables based on their 'plan', 'region' fields.

PR = Black[P2]&"-"&Black[Region]
PR = White[Plan]&"-"&White[Regionx]

2. Create a bridge table with union records.

Bridge =
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT ( UNION ( VALUES ( White[Plan] ), VALUES ( Black[P2] ) ) ),
        DISTINCT ( UNION ( VALUES ( Black[Region] ), VALUES ( White[Regionx] ) ) )
    ),
    "PR", [Plan] & "-" & [Region]
)

3. Create relationship between two table and bridge table.

 

After these you can use bridge table fields and other table value column to create graph or crete measures.

4.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)