Forum Discussion

Paro's avatar
Paro
Regular Visitor
5 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Paro
Regular Visitor
5 years ago

Hi Anonymous ,

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's avatar
Ashish_Mathur
Super User
5 years ago

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's avatar
    Paro
    Regular Visitor
    5 years ago

    Hi Ashish_Mathur

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      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.