Forum Discussion
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...
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,
KellyDid I answer your question? Mark my post as a solution!
Anonymous , oops ,
See if this can work
sumx(values(Table[Total kWh]),Table[Total kWh])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:
| Company | Site | Managed by Company A? | Total kWh |
| A | 1 | 100 | |
| A | 2 | 200 | |
| A | 3 | 150 | |
| A | 4 | 300 | |
| A | 5 | 150 | |
| B | 2 | Yes | 200 |
| B | 3 | Yes | 150 |
| B | 5 | Yes | 150 |
| B | 6 | No | 275 |
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