Forum Discussion

Himanshi0291's avatar
Himanshi0291
Helper II
6 years ago

Filtered value doubles values

Hi,

 

I've been facing the issue of double values in my query when i try to calculate sum of hours into projects with names = Administration, Administration HK and CH administration. 

 

My query is - 

Administration Hours = CALCULATE(SUM(DK_Time_Temp[RegHrs]), FILTER('DK_Projects','DK_Projects'[Name] = "Administration" || 'DK_Projects'[Name]= "CH Administration" || 'DK_Projects'[Name] = "Administration HK"))
 
The problem is that if there are hours in 'Administration' and no hours in CH administation or Administation HK even then my query adds hours of Administration twice or thrice. Looks like it is adding administration wherever it finds values. CH or HK administration both have 'Administration' word. 
 
How do i stop it from adding values and let it consider values of Just 'Administration' or 'CH Administration' or 'Administration HK'? Please help.
 
 
Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Himanshi0291 you have given or condition, if there are 3 rows of same project name with 3 options, it will sum up all three. it is doing as expected, what you want to achieve? Don't want to sum if the project as all 3 values? 

    • Himanshi0291's avatar
      Himanshi0291
      Helper II
      6 years ago

      Hi, I want that it should sum up all three if it has all three project names.

      But in my case I have noticed since all the three project names have a word "Administration" common in them it is summing it multiple times.

      For example, let suppose I want the sum of project named= "Administration" so instead of given me the result 1, it gives me 3 means it is picking the value from rest 2 also as they also have "Administration" in project name, which should not happen. How to fix this, please suggest.

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        6 years ago

        Hi , Himanshi0291 

         

        The problem is that if there are hours in 'Administration' and no hours in CH administation or Administation HK even then my query adds hours of Administration twice or thrice. Looks like it is adding administration wherever it finds values. CH or HK administration both have 'Administration' word. 

        I’m not  sure what you mean by this word . Filter 1 （project named= "Administration" ）, Filter2 (project named=Administration HK) and Filter3 (CH Administration)   are independent of each other, and there is no case of repeated counting.

         

        Can you  share some sample data with actual result in pbix  and  expected  result in excel ?

        Best Regards,
        Community Support Team _ Eason

         

         

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    Hi , Himanshi0291 

    Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.
    Best Regards,
    Community Support Team _ Eason