Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Subtract within column then divide

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! 

 

 

Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    7 years ago

    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 

     

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

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

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago

    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 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!