Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate Function Not Summing Correctly

Hello,

 

I have a pretty simple formula below that I want the sum to calculate based off the two criteria I have put in the filter. It seems that the sum is only taking into account the first filter option. Is there something wrong with the below formula that could be causing this?

 

2018 Employees =
CALCULATE(sum('SQL'[attributes.value]),
    FILTER('SQL','SQL'[attributes.lastModifiedDate] = [2018 Last Modified Date] && 'SQL'[attributes.periodEnd] = [Latest Date 2018]))
 
I know that the two measures I am comparing the values too are correct too. 
 
Any help on this will be very very much appreciated.
 
Best
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Anonymous Looks like you have a single table involved in your calculation. CALCULATE is not really good with dealing with just single tables. I would advise a No CALCULATE approach:

    2018 Employees =
  VAR __2018LastModifiedDate = [2018 Last Modified Date]
  VAR __LatestDate2018 = [Latest Date 2018]
  VAR __Table = FILTER(ALLSELECTED('SQL'),'SQL'[attributes.lastModifiedDate] = __2018LastModifiedDate  && 'SQL'[attributes.periodEnd] = __LatestDate2018 ))
  VAR __Return = SUMX(__Table, [attributes.value])
RETURN
  __Return

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Anonymous Looks like you have a single table involved in your calculation. CALCULATE is not really good with dealing with just single tables. I would advise a No CALCULATE approach:

    2018 Employees =
  VAR __2018LastModifiedDate = [2018 Last Modified Date]
  VAR __LatestDate2018 = [Latest Date 2018]
  VAR __Table = FILTER(ALLSELECTED('SQL'),'SQL'[attributes.lastModifiedDate] = __2018LastModifiedDate  && 'SQL'[attributes.periodEnd] = __LatestDate2018 ))
  VAR __Return = SUMX(__Table, [attributes.value])
RETURN
  __Return
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hey Greg_Deckler ,

       

      Thank you very much for the help. The meaure worked very well for all of my rows except one. It is showing an incorrect total for the row vs. what is in the underlying data. Do you know what could be casuing this?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        Anonymous Very tough to say. If it was showing something incorrect in the Total row, that would be one thing, is that what you are saying? Or is it an incorrect value in a row of a table?