Calculate Function Not Summing Correctly

[PeteSil1020]

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

[Greg_Deckler · Accepted solution]

@PeteSil1020 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
    

[PeteSil1020]

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]

@PeteSil1020 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?

[PeteSil1020]

Hey @Greg_Deckler ,

Please see the below images:

3 + 1 + 242 + 115 + 331 + 1 + 17 + 1 + 1 + 348 + 101 = 1,161

P.S. - 1,161 is the correct total I am expecting to see, but it isn't summing it at the asset row.

Instead it is showing the below photo:

[Greg_Deckler]

@PeteSil1020 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also, this specifically may help as well: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community