Calculate the different column total based on Filter value.

[MMadhusudhan]

Report contains data of multiple Countries.(For now only two countries i.e USA,CANADA) in Coutry_Name field. Created filter(multi selected) by using slicer to select Country names. We are having Local_Amount value, and Global_Value in table level.

If we select multiple countries(Multiple values in the filter) in the report filter, it must calculate sum(Global_Value), if we select one country then it must calculate the sum(Local_Value).

[Anonymous · Accepted solution]

Hi @MMadhusudhan ,

Is this result you want?

When I choose one country, the result is sum of “Local_value” , as the sample shows “9”,

When I choose two countries, it returns “6”, which is sum of “Global_value”:

If this result can meet your command, you can try this measure:

Measure = IF(COUNTROWS(VALUES('Table 1'[country_name]))>1 ,SUM('Table 2'[Global_value]),SUM('Table 2'[Local_value]))

And this is my testing pbix file, you also can view it for better understanding.

If I have misunderstood your meaning, please let me know in time。

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[technolog]

Please, could you sent us the screenshot=)

[daXtreme]

@MMadhusudhan 

Use the HASONEFILTER function to detect whether one or many countries have been selected.

[technolog]

You can try if condition:

Result = IF ( HASONEVALUE ( 'Table'[Country_Name] ) > 1 , Global_Value , Local_Amount )

or

Result = IF ( COUNTROWS ( VALUES ('Table'[Country_Name]) ) > 1 , Global_Value , Local_Amount )

[daXtreme]

HASONEVALUE is not the right function to use here since it works on cross-filters as well. HASONEFILTER works on direct filters only and this is precisely what this task calls for.