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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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'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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 
      thank you very much, your solution is working in my case.

  • 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.