Help With Custom Formula

[Anonymous]

Hi,

My data table  looks like this in Power BI. 

I want to use this formula in custom column.

([Test Post]-([Test Pre]+Number.Abs([Test Pre])*([Control Post]-[Control Pre])/Number.Abs([Control Pre])))/Number.Abs([Test Pre]+Number.Abs([Test Pre])*([Control Post]-[Control Pre])/Number.Abs([Control Pre]))

In custom formula it looks like 

Looking at the table in PowerBI , I want to refer each cell under variable and group columns so that each cell can be referred in the formula

For Example:  Formula with Revenue should include Control Post, Control Pre, Test Post and Test Pre.

Same formula must refer all the cells with Units. This formula has worked well but just need to refer the variables in custom formula.

Responses are appreciated

[wdx223_Daniel]

where will you refer these value?

say, your query name above is "RefTable", then you can refer these data as this

=Table.AddColumn(AnotherTableName,"Custom",each let a=RefTable{[Variable=[AnotherTable's Column Name which used to refer variable]]}? in if a=null then null else (a[Test Post]-(a[Test Pre]+Number.Abs(a[Test Pre])*(a[Control Post]-a[Control Pre])/Number.Abs(a[Control Pre])))/Number.Abs(a[Test Pre]+Number.Abs(a[Test Pre])*(a[Control Post]-a[Control Pre])/Number.Abs(a[Control Pre])))

[Anonymous]

I want to create a new custom column name "Lift" with the values calculated using the formula above. For example, in the above formula I want to refer Test Post ,Test Pre, Control Post and Control Pre for Revenue Variable and same for Units variable. 

[wdx223_Daniel]

if you just want to add a custom column in the same table, just put you code in custom formula area.

or, add this code as a new step after your pivot table step

=Table.AddColumn(PivotedTable,"lift",each ([Test Post]-([Test Pre]+Number.Abs([Test Pre])*([Control Post]-[Control Pre])/Number.Abs([Control Pre])))/Number.Abs([Test Pre]+Number.Abs([Test Pre])*([Control Post]-[Control Pre])/Number.Abs([Control Pre])))

[Anonymous]

I tried and it gives me syntax error. Can you Check what is wrong in it?

[wdx223_Daniel]

it will be useful if you learn more basic concepts of M code.

according the syntax of M, "Piovted Column" should be replaced by "Source"

but, i think it will not help.

this code should be used after your step which you have pivoted your original data

[wdx223_Daniel · Accepted solution]

suggest you to Pivot your table firstly, then you can use your formula in custom formula area. =Table.Pivot(PreviousStepName,List.Distinct(PeviousStepName[Group]),"Group","Average of Value")

[Anonymous]

Thank you for your response, I have created a pivot and this is how it looks

Now I need to refer Variable in the formula I want to use:

Like One for Revenue and One for Units with values in different column.

([Test Post]-([Test Pre]+Number.Abs([Test Pre])*([Control Post]-[Control Pre])/Number.Abs([Control Pre])))/Number.Abs([Test Pre]+Number.Abs([Test Pre])*([Control Post]-[Control Pre])/Number.Abs([Control Pre]))