Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

SUMMARIZE using column from another table

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 link...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

 

Could you share your file?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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