Forum Discussion

socksinbox's avatar
socksinbox
Icon for Helper I rankHelper I
7 years ago
Solved

Calculating difference between consecutive rows

How to calculate difference between count of Active & Negative Ppl for each segment in DAX e.g. in below table Blank segment has 3 active & 6 negative so the diff is -3 so the final table should l...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi socksinbox ,

     

    It can be segment wise, you just create a table and drag the segment column into it,

    then create the measue I did, then drag to it, you will see the result.

     

    Or do you mean you want to use a DAX to create a new table with segment column and diff column?

     

    Aiolos Zhao

  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi socksinbox ,

     

    If you want to create a new table, please use below expression :

    Table = SUMMARIZE(Table4,Table4[Segment],"Diff",CALCULATE(COUNTROWS(Table4),Table4[PeopleType] = "Active") - CALCULATE(COUNTROWS(Table4),Table4[PeopleType] = "Negative"))

    Thanks.

    Aiolos Zhao

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    Based on the data that you have shared, the average should be 0.  Try this measure

    Measure = AVERAGEX(SUMMARIZE(CALCULATETABLE(Data,Data[PeopleType]="Active"||Data[PeopleType]="Negative"),Data[Segment],"Active",CALCULATE(COUNTROWS(Data),Data[PeopleType]="Active"),"Negative",CALCULATE(COUNTROWS(Data),Data[PeopleType]="Negative")),[Active]-[Negative])

socksinbox's avatar
socksinbox
Icon for Helper I rankHelper I
7 years ago

Thanks Anonymous for this.

 

Actually this will calculate the overall difference, however, I would like to have segment wise difference. So I would need to return a table not a measure

 

thanks

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi socksinbox ,

 

It can be segment wise, you just create a table and drag the segment column into it,

then create the measue I did, then drag to it, you will see the result.

 

Or do you mean you want to use a DAX to create a new table with segment column and diff column?

 

Aiolos Zhao

  • socksinbox's avatar
    socksinbox
    Icon for Helper I rankHelper I
    7 years ago

    Yes.. I mean I would like to create a new data table.
    Actually, once I will have the segment-wise difference then I will create a measure on the top of that which will take the average of all differences and show in a card visual

     

    cheers

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi socksinbox ,

       

      If you want to create a new table, please use below expression :

      Table = SUMMARIZE(Table4,Table4[Segment],"Diff",CALCULATE(COUNTROWS(Table4),Table4[PeopleType] = "Active") - CALCULATE(COUNTROWS(Table4),Table4[PeopleType] = "Negative"))

      Thanks.

      Aiolos Zhao

      • socksinbox's avatar
        socksinbox
        Icon for Helper I rankHelper I
        7 years ago

        Thanks Anonymous  for your help

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      Based on the data that you have shared, the average should be 0.  Try this measure

      Measure = AVERAGEX(SUMMARIZE(CALCULATETABLE(Data,Data[PeopleType]="Active"||Data[PeopleType]="Negative"),Data[Segment],"Active",CALCULATE(COUNTROWS(Data),Data[PeopleType]="Active"),"Negative",CALCULATE(COUNTROWS(Data),Data[PeopleType]="Negative")),[Active]-[Negative])