Forum Discussion

Cocotip926's avatar
Cocotip926
Frequent Visitor
7 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 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'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

4 Replies

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

    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's avatar
      Cocotip926
      Frequent Visitor
      7 years ago

      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'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