Calculate difference in statuses

[FabioC86]

Hi all,

I have a dataset table like the one below:

ID	Initial Status	Final Status
a1	x	x
a2	y	x
a3	z	y

 and I have to create a visualization like this one:

Initial Status	Difference
x	(number of IDs with final status = x) - (number of IDs with initial status = x)
y	(number of IDs with final status = y) - (number of IDs with initial status = y)
z	(number of IDs with final status = z) - (number of IDs with initial status = z)

How can I calculate the difference? Thank you in advance.

Fabio

[johnt75 · Accepted solution]

You can create a measure like

Difference =
var currentStatus = SELECTEDVALUE( 'Table'[initial value])
var numFinalStatus = CALCULATE( COUNTROWS('Table'), REMOVEFILTERS('Table'[initial status]), 
   'Table'[final status] = currentStatus )
var numCurrentStatus = COUNTROWS('Table')
return numFinalStatus - numCurrentStatus

[Jihwan_Kim]

Hi,

please check the below picture and the attached pbix file.

Difference measure: =
IF (
    HASONEVALUE ( Data[Initial Status] ),
    COUNTROWS (
        FILTER ( ALL ( Data ), Data[Final Status] = MAX ( Data[Initial Status] ) )
    )
        - COUNTROWS (
            FILTER ( ALL ( Data ), Data[Initial Status] = MAX ( Data[Initial Status] ) )
        )
)