Difference by column values

[Anonymous]

Hello All, 

I have sitiation where i have to caliculate the diffrence 

Category	Substation	Project phase	Planned Cost
Grid Automation	Ardmore	PLAN	515972.7981
Grid Resiliency	Ardmore	PLAN	1137192.94
Grid Automation	Bellcow	MODEL	1114147.5
Grid Automation	Bellcow	PLAN	1174533.554
Grid Resiliency	Bellcow	MODEL	2500330
Grid Resiliency	Bellcow	PLAN	2157894.014
Grid Automation	Belle Isle Sta	MODEL	1657040
Grid Automation	Belle Isle Sta	PLAN	1550595.136
Grid Resiliency	Belle Isle Sta	MODEL	1538123.351
Grid Resiliency	Belle Isle Sta	PLAN	1631975.692

now I have to caluclate the diffrence between plan and model for each Category and Substation

Example 

for Category : Grid Automation = (sum of cost of plan ) - (sum of cost of model )

and also for Subsation: Bellcow = (sum of cost of plan ) - (sum of cost of model )

but now the Subsation Ardmore has no  MODEL so the difference will be just sum of cost of plan 

Thanks

RP

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ，

Based on your description, you can do some steps as follows。

1. Create a calculated index column.

index =

RANKX (

    FILTER (

        CASE,

        EARLIER ( CASE3[Category] ) = CASE[Category]&&EARLIER('CASE'[Substation])='CASE'[Substation]

    ),

    'CASE'[Project phase],

    ,

    DESC

)

1. Create a measure.

Measure =

var x1 = CALCULATE(MAX('CASE'[Planned Cost]),FILTER(ALLEXCEPT('CASE',CASE[Category],CASE[Substation]),[index]=1))

var x2 = CALCULATE(MAX('CASE'[Planned Cost]),FILTER(ALLEXCEPT('CASE',CASE[Category],CASE[Substation]),[index]=2))

return

x1-x2

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try measure like

sumx(filter(Table[Project phase] = "PLAN"),Table[Planned Cost]) -sumx(filter(Table[Project phase] = "MODEL"),Table[Planned Cost])

or
sumx(Table, if( Table[Project phase] = "PLAN" , Table[Planned Cost] , -1 *Table[Planned Cost]))