Returning one value when values in two other columns are the same

[Moco]

I have the following data structure

Name	Row	Format	Value
A	0	X	10
B	0	X	12
C	0	X	9
D	0	X	11
A	1	Y	5
B	1	Y	7
C	1	Y	6
D	1	Y	4

I need a calculated column that shows the [Value] (which is a measure) for [Name] A for all instances where [Row] and [Format] are the same so

A | 0 | X | 10 | 10

B | 0 | X | 12 | 10

C | 0 | X | 9  | 10

D | 0 | X | 11 | 10

A | 1 | Y | 5 | 5

B | 1 | Y | 7 | 5

C | 1 | Y | 6 | 5

D | 1 | Y | 4 | 5

I've tried using SUMMARIZE and GROUPBY but I can't get A[Value] to appear against B C & D in the new column.

Ultimately I need to find the percentage difference between the new column and [Value]. I can work that out if I can populate the new column correctly but there may be a better way.

Would appreciate some help please.

[tamerj1 · Accepted solution]

Hi @Moco 

please try the following calculated column 

NewColumn =
VAR CurrentRowFormatTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Row], 'Table'[Format] ) )
VAR ATable =
FILTER ( CurrentRowFormatTable, 'Table'[Name] = "A" )
VAR Cost =
SUMX ( ATable, 'Table'[CostA] + 'Table'[CostB] )
RETURN
Cost * 1.03 * 1.04

[FreemanZ]

hi @Moco 

try to create a calculated table like:

TableA = 
SELECTCOLUMNs(
    FILTER(
        TableName,
         TableName[Name]="A"
    ),
    "Row", TableName[Row],
    "Format", TableName[Format],
    "Value", TableName[Value]
)

then add a calculated column in your original table like:

Column = 
LOOKUPVALUE(
    TableA[Value],
    TableA[Row], TableName[Row],
    TableA[Format], TableName[Format]
)

it worked like:

[Moco]

The calculated table has worked but I am getting an error 'A circular dependency was detected...' for the calculated column. 

[FreemanZ]

hi @Moco 

could you paste the code the calculated table?

[Moco]

Using different device so can't paste code but this is what has produced the correct table:

baselinecomp =

SELECTCOLUMNS(

        FILTER(

               TableName,

               TableName[Name] = A

         ),

         "Row", TableName [Row],

         "Format", TableName [Format],

         "Value", CALCULATE((SUM(TableName [CostA])

          +SUM(TableName[CostB]))*(1.03)*(1.04)))

I presume the issue is something to do with my final expression!