Forum Discussion
Calculate the different column total based on Filter value.
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).
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
5 Replies
- technolog
Super User
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 )
daXtremeSolution Sage
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.
- daXtreme
- technolog
Please, could you sent us the screenshot=)
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
