SUMMARIZE using column from another table

[Anonymous]

Hi,

I am trying to summarize a table across Regions and Categories for the amont of Spend, but then I also want to add another column to the grouping for the 'Asset' which comes from a table linked to my 'Visibility Data - All Regions' table.

How would I best go about doing this? I was hoping I could somehow use the RELATED function but having no luck.

Below is my current table without Asset added in.

SUMMARIZE('Visibility Data - All Regions',
                        'Visibility Data - All Regions'[Region],
                        'Visibility Data - All Regions'[Group],
                        'Visibility Data - All Regions'[Category],
                        'Visibility Data - All Regions'[Sub Category],
                        "Revised Spend (USD)",
                        sum('Visibility Data - All Regions'[Revised Spend (USD)])
                         )

Thanks,

TG

[Zubair_Muhammad · Accepted solution]

@Anonymous

If that other table with Asset column is on the side of the relationship, then you can simply add it as GROUPING Column

=
SUMMARIZE (
    'Visibility Data - All Regions',
    'Visibility Data - All Regions'[Region],
    'Visibility Data - All Regions'[Group],
    'Visibility Data - All Regions'[Category],
    'Visibility Data - All Regions'[Sub Category],
    OtherTableName[AssetColumn],
    "Revised Spend (USD)", SUM ( 'Visibility Data - All Regions'[Revised Spend (USD)] )
)

[Anonymous]

Hi Zubair,

Thanks for the quick response but I tried that and I get the issue "The column 'Facility / Asset Name' specified in the 'SUMMARIZE' function was not found in the input table."

FYI - the data already being summarized is linked to the table containing Facility / Asset Name but on another column.

Any ideas?

TG

[Zubair_Muhammad]

@Anonymous

Could you share your file?

[Anonymous]

Hi Zubair,

Afraid I cannot as the data is very sensitive client data.

I have a main fact table containing the Region, Category, and Spend, as well as Cost Centre, I then have a seprate mapping table to map the Cost Centres to Assets. The same aggregation works fine in a front end chart not sure why I am getting this error when doing it in DAX.

Thanks,
TG

[v-jiascu-msft]

Hi @Anonymous,

Is the column in the ONE side of the relationship? If so, you can add it in summarize without any errors. Can you share a DUMMY sample that can reproduce the issue?

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft,

Ah, I actually cleaned up my data model yesterday as I was previously using the new Many-to-Many relationship option for just one of the tables (the one I needed to get the extra column from!).

Trying the same code @Zubair_Muhammad suggested yeterday has worked perfectly! Is there a reason why this won't work on the MANY side?

Thank you both for you help.

TG

[v-jiascu-msft]

Hi @Anonymous,

As we can see from the definition on this site, SUMMARIZE returns totals over a set of groups. So what can we expect the totals of one-side from the many-side? 

I'm glad you solved the issue.

Best Regards,
Dale