Forum Discussion

Cocotip926's avatar
Cocotip926
Frequent Visitor
8 years ago
Solved

calculate category total in table visualization

My source table 'Info' has several columns, [Client], [Country], [Year], [ProductCode] and [InvestAmount]. One client invest in several products, one client belongs to one country. Two years 2017 and...
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Cocotip926,

     

    I made one sample based on your data. Actually you can upload the file you want to share to dorop box and share the link here.

     

    1. Enter the data as you shared and create the relationship between the two tables.

     

     

    2. create a calculated column based on the info table.

     

    CN = RELATED(Country[Country_Name])

     

    3. Create the measure to get the result as you excepted.

     

    Measure = CALCULATE(SUM(Info[Invest_Amount]),ALLEXCEPT(Info,Info[CN]))

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Cocotip926,

 

I made one sample based on your data. Actually you can upload the file you want to share to dorop box and share the link here.

 

1. Enter the data as you shared and create the relationship between the two tables.

 

 

2. create a calculated column based on the info table.

 

CN = RELATED(Country[Country_Name])

 

3. Create the measure to get the result as you excepted.

 

Measure = CALCULATE(SUM(Info[Invest_Amount]),ALLEXCEPT(Info,Info[CN]))

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Cocotip926's avatar
Cocotip926
Frequent Visitor
7 years ago

Thank you so much! It works! But could you please explain why do I need this new "related" column? I already have the relationship defined between tables. Thanks!