calculate category total in table visualization

[Cocotip926]

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 2018 are in the table. Now I created a table visualization using [Client], [Country], [Year] and [InvestAmount]. This visualization automatically aggregates InvestAmount on Client, so that every client is distinct in it.

Now I want to create a measure that aggregates InvestAmount on Country, and add this measure to the table visualization, so that every row with the same Country value displays same InvestAmount. I wrote the measure as:

[Country Total] = CALCULATE( SUM('Info'[InvestAmount]), ALLSELECTED('Info'[Country]))

It did not give the desired result, but expanded the table by adding extra rows, i.e combinations of each client with every countries.

What's wrong with my measure, please?

[v-frfei-msft]

Hi @Cocotip926,

Please try to use this formula. If it doesn't meet your requirement, kindly share your sample data and excepted reuslt to me.

[Country Total] = CALCULATE( SUM('Info'[InvestAmount]), ALLSELECTED('Info'))

Regards,

Frank

[Cocotip926]

Thank you! I didn't mention one thing. I have two tables, the other is a 'Country' table. 'Info' and 'Country' are related by [Country_Code]. I'm creating the table visualization with the two tables. Please take a look at the screenshots of my tables and report. (Don't know how to upload the whole file)

I'm trying to add the measure "Country Total" to my visualization, so that it can display the total investment amount for each country regardless of client.

eg. For both product A and E rows (both come from Argentina), in Country Total column, they should display 67. But using the measure 

Spoiler

Country Total = CALCULATE(SUM(Info[Invest_Amount]),ALLSELECTED(Country))

The visualization is displayed like this:

Thanks!

---

[v-frfei-msft · Accepted solution]

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]

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!