Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Loca...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

technolog's avatar
technolog
Icon for Super User rankSuper User
4 years ago

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's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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.