Create a Calculated Column/measure from Different Tables

[Kelly_Zhu]

Hi Everyone,

The relationship between 2 tables is 1: many.

How can I get the table I would like? Thank you in advance!

[Zubair_Muhammad]

hI @Kelly_Zhu

Use this calculated column in Table A

G =
DIVIDE ( Table A[E], RELATED ( Table 2[F] ) )

[Kelly_Zhu]

Thanks a lot!

However, after I input "Related", I can't input the column name then. This may be caused by "one to many" relationship.

Could you have any advices to solve this? Thanks!

[Zubair_Muhammad]

Hi @Kelly_Zhu

RELATED is used in Many-Side Table to pull information from ONE-SIDE. Its like a VLOOKUP

It works fine when I used your sample data. See the image below

[Kelly_Zhu]

Thanks a lot!

I used this as a measure before, so it doesn't work.

Now it works well.

However, I meet a new problem now. Could you help me to solve this? I would like to get the column/measure filled in grey.

Thank you in advance!

[Zubair_Muhammad]

Hi @Kelly_Zhu

Try this calculated column

=
IF (
    TableA[GROUP] <> "Total",
    RELATED ( Table2[F] ),
    IF (
        TableA[TOTAL GROUP] = "One",
        CALCULATE (
            SUM ( Table2[F] ),
            FILTER ( ALL ( Table2 ), Table2[GROUP] = "A" || Table2[GROUP] = "B" )
        ),
        IF (
            TableA[TOTAL GROUP] = "Two",
            CALCULATE ( SUM ( Table2[F] ), FILTER ( ALL ( Table2 ), Table2[GROUP] = "C" ) )
        )
    )
)

[Kelly_Zhu]

Thanks for your reply!

I am sorry for confusing explain.

Actually, the data I provided is an example and left table is the table I want to display on the report.

So there is no "Total" in any coolumns and more than 2 total group actually.

Could you please give me other suggestions?