Forum Discussion

johnf's avatar
johnf
Helper I
8 years ago
Solved

Remove duplicated rows in SUM calculation

Hello,   I'm having some issues in trying to work out how to remove duplicate rows from a calculation.   I have the following dataset (highly simplified from actual)   KEY NAME JOB PAYMEN...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    OK, try this:

     

    Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Not sure, I recreated your table exactly, can you post your formula?

 

johnf's avatar
johnf
Helper I
8 years ago

Greg_Deckler

 

I think this is because when passing the DISTINCT there's no condition applied to provide a boolean value for FILTER to use.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    OK, try this:

     

    Measure = SUMX(SUMMARIZE(DistinctSum,[KEY],"Payment",AVERAGE(DistinctSum[PAYMENT])),[Payment])
    • aschillinger20's avatar
      aschillinger20
      Helper II
      6 years ago

      Greg_Deckler This seems like a working answer for an issue i'm having except i dont see the option for "DistinctSum". My data structured like this: 

       

      Order Number |  Hours 

      123544             |      2

      123544             |      2

      178113             |      1

      199911             |      5

       

      I would like to remove the duplicate 123544 from my sum to see 8 as the total instead of 10.