SUMMARIZECOLUMNS return all columns in Table_1

[peterhui50]

I have two tables. They are both joined via the column "Store" and it's a 1:Many relationship.

1 being Table_1 and many being Table_2

Table_1

Table_2

I wrote 

SUMMARIZECOLUMNS(
    Table_1[Store],
    Table_1[Region],
    Table_2[Sales],
    "Totals",SUM(Table_2[Sales]))

but the result I want is this.

I have tried using the IGNORE() function but it returns not the correct results.

Any help would be appreciated!

Peter

[AlexisOlson · Accepted solution]

Using the Sales column as a dimension is a bit odd, IMO.

I'd suggest this if you want zeros:

SUMMARIZECOLUMNS(
    Table_1[Store],
    Table_1[Region],
    "Totals", SUM(Table_2[Sales]) + 0
)

Or this if you want blanks instead of zeros:

Test =
SUMMARIZECOLUMNS (
    Table_1[Store],
    Table_1[Region],
    "Totals", IGNORE ( SUM ( Table_2[Sales] ) )
)

[peterhui50]

Thanks Alexis!

[Anonymous]

Hi @peterhui50 ,

You can write the code like this in order to get Store and Regions with no sales:

SUMMARIZECOLUMNS(
            Table_1[Store],
            Table_1[Region],
            "Totals", IF( ISBLANK(SUM(Table_2[Sales])) , 0 , SUM(Table_2[Sales]) )
)

Jesus.