Forum Discussion
Difference between sum matching id
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.
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
9 Replies
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-SumotherHi Anonymous ,
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?
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?
