Calculating difference between consecutive rows

[socksinbox]

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

[AiolosZhao · Accepted solution]

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

[AiolosZhao · Accepted solution]

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

[AiolosZhao]

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

[socksinbox]

Thanks @AiolosZhao 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

[AiolosZhao · Accepted solution]

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]

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

[Ashish_Mathur · Accepted solution]

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]

Thanks @Ashish_Mathur  for your help

[Ashish_Mathur]

You are welcome.

[AiolosZhao · Accepted solution]

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]

Thanks @AiolosZhao  for your help

[AiolosZhao]

Glad to help you ~