Calculate duplicate data

[Anonymous]

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

[amitchandak]

@Anonymous , Try a measure like

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

[Anonymous]

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

[amitchandak]

@Anonymous , oops ,

See if this can work

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

[Anonymous]

@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

[Anonymous · Accepted solution]

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!

[Anonymous]

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?

[v-kelly-msft]

Hi @Anonymous ，

If dont use [Managed by Company A?] column ,how to know that which value needs to be removed?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!