Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate duplicate data

Here are my data:   Company Site Managed by Company A? Total kWh A 1   100 A 2   200 A 3   150 A 4   300 A 5   70 B 2 Yes 200 B 3 Yes 150 B 5 Ye...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ，

     

    Create 3 measures as below:

     

    Sum of kWh for Company A = SUMX(FILTER(ALL('Table'),'Table'[Company]="A"),'Table'[Total kWh])
    Sum of kWh for Company B = SUMX(FILTER(ALL('Table'),'Table'[Company]="B"),'Table'[Total kWh]) 
    Sum of kWh for Group X = SUMX(ALL('Table'),'Table'[Total kWh])-SUMX(FILTER(ALL('Table'),'Table'[Company]="B"&&'Table'[Managed by Company A?]="Yes"),'Table'[Total kWh])

     

    And you will see:

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , Try a measure like

sumx(Table,distinctcount(Table[Total kWh]))

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak I don't think that's correct because you're using DISTINCTCOUNT 😅

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , oops ,

    See if this can work

    sumx(values(Table[Total kWh]),Table[Total kWh])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak It's working, but I realised that you're using VALUES. This one only works when the column has distinct values, right? It's because when I changed the data a little bit, I did not get my desired result. You can try with this new data instead:

       

      CompanySiteManaged by Company A?Total kWh
      A1 100
      A2 200
      A3 150
      A4 300
      A5 150
      B2Yes200
      B3Yes150
      B5Yes150
      B6No275

       

      For this new data, my desired result would be:

      Sum of kWh for Company A = 900

      Sum of kWh for Company B = 775

      Sum of kWh for Group X = 1,175

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ，

         

        Create 3 measures as below:

         

        Sum of kWh for Company A = SUMX(FILTER(ALL('Table'),'Table'[Company]="A"),'Table'[Total kWh])
        Sum of kWh for Company B = SUMX(FILTER(ALL('Table'),'Table'[Company]="B"),'Table'[Total kWh]) 
        Sum of kWh for Group X = SUMX(ALL('Table'),'Table'[Total kWh])-SUMX(FILTER(ALL('Table'),'Table'[Company]="B"&&'Table'[Managed by Company A?]="Yes"),'Table'[Total kWh])

         

        And you will see:

        For the related .pbix file,pls click here.

         

         
        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!