Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Moco
Frequent Visitor

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

I have the following data structure

 

NameRowFormatValue
A0X10
B0X12
C0X9
D0X11
A1Y5
B1Y7
C1Y6
D1Y4

 

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.

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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
Super User
Super User

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]
)

FreemanZ_0-1678492510533.png

 

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:

FreemanZ_1-1678492542988.png

 

Moco
Frequent Visitor

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

hi @Moco 

could you paste the code the calculated table?

Moco
Frequent Visitor

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!

               

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.