Subtract within column then divide

[Anonymous]

Hi, trying to write a measure for Variance%. There are lots of Date, First, Last and Shop which need to be matched before proceed to calculate the Variance %. Column Status will only consist of "Quote", "Actual" and "Forecast". Calculation is shown at right, i.e. Variance % = Quote minus Actual then divide by Quote; Forecast minus Actual then divide by Forecast

Gurus out there, may you please help me! 

[Anonymous]

@parry2k , yes there is no common identifier.

@AnthonyTilley , you got it. This is what I want. New to DAX, will need to digest your solution. Thank you so much!

[AnthonyTilley · Accepted solution]

so i would do this with two parts 

1 - create a calculated colunm for Key that is a uniqu key for each iteration of date,last,first,shop

KEY = [Date] & "-" & [First] & "-" & [Last] & "-" & [Shop]

next create a measure as below

Measure = 
-- first define the key filed and status as veriables
var k = max('Table'[KEY])
var s = max('Table'[Status])
-- then caluclate the actual, forecast and qoute
var a = CALCULATE(sum('Table'[Value]), 'Table'[Status]= "actual", 'Table'[KEY] = k)
var f = CALCULATE(sum('Table'[Value]), 'Table'[Status]= "Forecast", 'Table'[KEY] = k)
var q = CALCULATE(sum('Table'[Value]), 'Table'[Status]= "Qoute", 'Table'[KEY] = k)
-- then calculate each of the possable values
var v1 = 0
var v2 = (a-f)/f
var v3 = (a-q)/q
--finaly switch values based on defined status
var ret = SWITCH(s,
                    "ACTUAL",v1,
                    "FORECAST",v2,
                    "QOUTE",v3,0)

return ret

simple put the measure first defines the two dimensions the key and the status 

it then calcualtes the actual, forecast and qoute for each of these dimentions and stores them in veriables 

it then usees these three veriables to create a set of values based on your calculations

the final step then switches between each of these values based on the status in the row 

[parry2k]

@Anonymous do you have common identifier for each group or just name is the identifier to group rows together?