Forum Discussion
Calculate duplicate data
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!
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
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
Hi Anonymous,
Thank you for your help!
I'm just curious, is it possible to get the result that I want without using [Managed by Company A?] column?
Hi Anonymous ，
If dont use [Managed by Company A?] column ,how to know that which value needs to be removed?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
