topic Re: Calculate the different column total based on Filter value. in DAX Commands and Tips

Calculate the different column total based on Filter value.

Anonymous — Mon, 25 Jul 2022 10:48:58 GMT

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).

Re: Calculate the different column total based on Filter value.

technolog — Mon, 25 Jul 2022 12:06:06 GMT

You can try if condition:

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

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

Re: Calculate the different column total based on Filter value.

daXtreme — Mon, 25 Jul 2022 12:06:34 GMT

Anonymous

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

Re: Calculate the different column total based on Filter value.

technolog — Mon, 25 Jul 2022 12:08:25 GMT

Please, could you sent us the screenshot=)

Re: Calculate the different column total based on Filter value.

Anonymous — Thu, 28 Jul 2022 08:57:52 GMT

Hi Anonymous ,

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

Re: Calculate the different column total based on Filter value.

daXtreme — Thu, 28 Jul 2022 09:29:27 GMT

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.