To get overall sum of value despite of filter selection

[Saurabh07]

Hi, Mentioned is the structure of my dataset;

Company_Name	ID	Country	Value
Company A	1234	India	50
Company A	1234	Japan	60
Company B	4567	India	20
Company B	4567	Japan	70

I have a filter at top to select Country. If I select 'Country = India' in the filter below is the desired output. In 'Company_Total' the value of India should be shown for each of the Company, but 'Total_Value' should display the overall sum of Value for each counterparty irrespective of the country.

Company_Name	ID	Company_Total	Total_Value
Company A	1234	50	110
Company B	4567	20	90

I tried using the mentioned expression for 'Total_Value' column, sum({<Country= >}Value)

But its not working! Can anyone please help me out here?

[sunny_talwar]

This should have worked

Sum({<Country>} Value)

Do you have Country as one of your dimension? If you do, may be you will need this

Sum(TOTAL <Company_Name> {<Country>} Value) * Avg(1)

[Saurabh07]

Hello @sunny_talwar , Thank you for the reply.

I tried this earlier, the value is correct in 'Total_Value' and 'Company_Total' columns, but all the other Company names are displayed in the table. eg. If India is selected in filter, Company C which doesn't belong to India is displayed in the table, though the value is shown as '-' in 'Total_Value' column, I don't want to see this Company C when India is selected.

[sunny_talwar · Accepted solution]

@Saurabh07 May be try this

Sum({<Country, Company_Name = p(Company_Name)>} Value)

[Saurabh07]

@sunny_talwar Thank you so much. This worked