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 | Yes | 70 |
| B | 6 | No | 275 |
Company A and B are under Group X. I would like to calculate the sum of kWh. Group X can view all data from company A and B, but company A and B can only view their own data only (I'm using row-level security here). I'm having problem when calculating sum of kWh for Group level because this will cause duplication.
My desired result would be:
Sum of kWh for Company A = 820
Sum of kWh for Company B = 695
Sum of kWh for Group X = 1,095
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!
7 Replies
Anonymous , Try a measure like
sumx(Table,distinctcount(Table[Total kWh]))
amitchandak I don't think that's correct because you're using DISTINCTCOUNT 😅
Anonymous , oops ,
See if this can work
sumx(values(Table[Total kWh]),Table[Total kWh])
