Difference between sum matching id

[Paro]

Hi everybody,

hope anybody can helping me solving that problem. I need to calculate the difference of two values but only when mycompany matches the id, like in this table down below. 

ID	Company	Position	Sum	Result
1	Company1	1	10000
1	Company2	2	20000
1	Company3	3	30000
1	MyCompany	4	40000	30000
2	Company1	1	50000
2	Company2	2	60000
2	Company3	3	70000
3	Company1	1	80000
3	MyCompany	2	90000	10000
4	MyCompany	1	100000	0
4	Company1	2	110000

I have created two measures, first get the sum of mycompany when position >1

SumMyCompany = 
CALCULATE( SUM('Table'[Sum]), filter('Table',find("MyCompany", 'Table'[Company],,0)<>0), 'Table'[Position]>1)

and the other will get the sum of the first place

SumOther = 
CALCULATE( SUM('Table'[Sum]), filter('Table',not(find("MyCompany", 'Table'[Company],,0)<>0)), 'Table'[Position]=1)

SumMyCompany - SumOther = difference; but in this case it also calculate the difference when MyCompany doesn't match the id.

Is there an easy way to filter only the range of id's where mycompany matches?

I'm really thankful for every suggestion to solving my problem.

[Vera_33]

Hi @Paro 

You want a measure to display in above table visual? It looks at the ID in the visual

Measure = 
VAR CurID = SELECTEDVALUE('Table'[ID])
VAR SumMyCompany =
SUMX(FILTER(ALL('Table'),'Table'[Company]="MyCompany"&&'Table'[Position]>1&&'Table'[ID]=CurID),'Table'[Sum])
VAR Sumother=
SUMX(FILTER(ALL('Table'),'Table'[Company]<>"MyCompany"&&'Table'[Position]=1&&'Table'[ID]=CurID),'Table'[Sum])
RETURN
SumMyCompany-Sumother

[Paro]

Hi @Vera_33 ,

thanks for your response. This measure is a basis for the calculation of an avarage. Unfortunately I want to show a single value in a card kpi. When I try the measure above it shows me blank, the issue ist the ID. When its selected the measure won't be calculated. Is there a opportunity to get a single value?

[Vera_33]

Hi @Paro 

The measure was based on the assumption you were using it in a table visual...so what is the single value you want to display in a Card? Sum of all the difference of all the IDs?

[Paro]

Hi @Vera_33 ,

sorry for my bad description, the table above should only be an example.

Yes I just want to show a single Value of sum of all the difference, but only for these IDs which my company is involved.

[Ashish_Mathur]

Hi,

So the result that you are expecting in a card visual is (40,000-30,000)+(90,000-10,000) = 90,000.  Am i correct?

[Paro]

Hi @Ashish_Mathur

thanks for your interest, the result i expecting is (40000-30000) + (90000-80000) = 40000

[Ashish_Mathur]

Hi,

This measure works

=SUMX(FILTER(SUMMARIZE(VALUES(Data[ID]),Data[ID],"ABCD",CALCULATE(sum(Data[Sum]),Data[Company]="MyCompany"),"EFGH",CALCULATE(sum(Data[Sum]),Data[Position]=1)),[ABCD]>0),[ABCD]-[EFGH])

Hope this helps.

[Vera_33 · Accepted solution]

Hi @Paro 

So you need to sum up the difference of all IDs with your logic, please add a Calculated DAX column of this result, then you can simply SUM this column to display in a Card. But it might be slow if you have a lot of data, then you can consider using M to pre-calculate

Column = 
VAR CurID = 'Table'[ID]
VAR SumMyCompany =
SUMX(FILTER(ALL('Table'),'Table'[Company]="MyCompany"&&'Table'[Position]>1&&'Table'[ID]=CurID),'Table'[Sum])
VAR Sumother=
SUMX(FILTER(ALL('Table'),'Table'[Company]<>"MyCompany"&&'Table'[Position]=1&&'Table'[ID]=CurID),'Table'[Sum])
RETURN
SumMyCompany-Sumother

[Paro]

Hi @Vera_33 
thank you very much, your solution is working in my case.