How to create a metric that cross the results of two tables?

[rcmv85]

Hi!

I have two tables in my Power Query Editor which after some transformations look like this:

Table1:

Company	Symbol	Units
company_name_a	A	All numbers in thousands
company_name_b	B	All numbers in thousands
company_name_c	C

Table2:

Financial KPI	Year	Company_Name	Value		Symbol
KPI_A	2022	Company_A	158057000		A
KPI_A	2022	Company_B	null		B
KPI_A	2022	Company_C	null		C
KPI_A	2021	Company_A	136341000		A
KPI_A	2021	Company_B	46213000		B
KPI_A	2021	Company_C	250199000		C
KPI_A	2020	Company_A	127144000		A
KPI_A	2020	Company_B	43475000		B
KPI_A	2020	Company_C	222884000		C
KPI_A	2019	Company_A	155900000		A
KPI_A	2019	Company_B	55537000		B
KPI_A	2019	Company_C	252633000		C
KPI_B	2022	Company_A	-1981000		A
KPI_B	2022	Company_B	null		B
KPI_B	2022	Company_C	null		C
KPI_B	2021	Company_A	17937000		A
KPI_B	2021	Company_B	888000		B
KPI_B	2021	Company_C	15382000		C
KPI_B	2020	Company_A	-1279000		A
KPI_B	2020	Company_B	-8008000		B
KPI_B	2020	Company_C	8867000		C
KPI_B	2019	Company_A	47000		A
KPI_B	2019	Company_B	-141000		B
KPI_B	2019	Company_C	13886000		C

Now, I have some measures I've created to display some of these KPIs. One of the looks like this:

_my_metric = CALCULATE(
    SUM(Table2[Value]), 
    Table2[Financial KPI]="KPI_A"
    )

It works OK but I would like to make this metric more accurate in a sense that, depending on the company, the result of this metric may have to be multiplied by 1000 since as you can see on Table1, for some companies, the values are in thousands. So I would like to change this metric with some 'if' conditions that allow me to multiply the KPI values by 1000 when the 'Units' column of Table1 has the string "All numbers in thousands" and leave the value as is, when the 'Units' column is blank. I believe I can link the two tables using the 'Symbol' column as a reference but I'm not sure how to do this. 

Any help?

I tried something like the formula below but got no joy. It says that Table2[Symbol] either doesn't Exist or doesn't have a relationship to any table available in the current context.

_my_metric = 
VAR units_multiplier = 
    IF(
        SELECTEDVALUE(Table1[Units]) = "All numbers in thousands",
        1000,
        1
    )
RETURN
    CALCULATE(
        SUM(Table2[Value]) * units_multiplier, 
        Table2[Financial KPI]="KPI_A",
        RELATED(Table2[Symbol]) = Table1[Symbol]
    )

[Ahmedx · Accepted solution]

try like this:

try like this:

_my_metric = 
VAR _units_multiplier = CALCULATE(MAX('Table1'[Units]),TREATAS(VALUES(Table2[Symbol]), Table1[Symbol]))
VAR _Result=
    IF(
         _units_multiplier= "All numbers in thousands",
        1000,
        1
    )
    RETURN
      CALCULATE(
        SUM(Table2[Value]) * _Result, 
       KEEPFILTERS( Table2[Financial KPI]="KPI_A")
    )
  

[rcmv85]

Thank you @Ahmedx !

It worked perfectly!