Forum Discussion
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 look this
Segment | difference
Blank | -3
etc
| PeopleType | Segment |
| Active | BLANK |
| Active | BLANK |
| Active | BLANK |
| Negative | BLANK |
| Negative | BLANK |
| Negative | BLANK |
| Negative | BLANK |
| Negative | BLANK |
| Negative | BLANK |
| Passive | BLANK |
| Passive | BLANK |
| Active | EC |
| Active | EC |
| Passive | EC |
| Active | Resident |
| Active | Resident |
| Active | Resident |
| Negative | Resident |
| Negative | Resident |
| Passive | Resident |
| Active | SME |
| Active | SME |
| Negative | SME |
| Negative | SME |
| Passive | SME |
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
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
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])
10 Replies
Hi socksinbox ,
Please try to use below measure, it works in my demo :
CALCULATE(COUNTROWS(Table4),Table4[PeopleType] = "Active") - CALCULATE(COUNTROWS(Table4),Table4[PeopleType] = "Negative")
Thanks.
Aiolos Zhao
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
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
