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 ,

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?

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