Cross 2 table to calculate

[Anonymous]

Hi everyone,

I got a problem...it seems so simple but I actually cannot solve it 😞

Table 1

N°           Amount

C1            x

C2            y

Table 2

N°            Currency factor

C1             0

C2             1.12

And I would like :

Report Table

N°          Total Amount (if currency factor=0 => Amount, else => Amount/CurrFactor)

C1           x

C2           y/1.12

And I would like to have the sum = x+y/1.12

Is it easy?

Thank you so much

[v-yulgu-msft]

Hi @Anonymous,

If there is an existing relationship between 'Table1' and 'Table2', you could take Zubair_Muhammad's suggestion which can return expected result.

If there is no relationship between tables, you could try:

Total Amount =
IF (
    LOOKUPVALUE ( Table2[Currency Factor], Table2[N], Table1[N] ) = 0,
    Table1[Amount],
    Table1[Amount] / LOOKUPVALUE ( Table2[Currency Factor], Table2[N], Table1[N] )
)

When you add 'Table1'[N] and 'Table1'[Total Amount] into a table visual, it will automatically calculate the sum in Total row.

Best regards,

Yuliana Gu

[Anonymous]

I can't choose Table1[N]...only the measures from the Table1...

So I create a measure on the Table1 with SUM(Amount).

Then, the LOOKUP issue doesn't work neither... 😞 Error!

[Zubair_Muhammad]

@Anonymous

Assuming there is a relationship between Table1 and Table2,

add this calculated column in Table1

Total Amount =
IF (
    RELATED ( Table2[Currency Factor] ) = 0,
    Table1[Amount],
    DIVIDE ( Table1[Amount], RELATED ( Table2[Currency Factor] ) )
)

[Anonymous]

There is an active relationship (on N°) but it reacts as it does not. "RELATED" doesn't work...

So I can't write this lines (and I don't know why)...

[v-yulgu-msft]

Hi @Anonymous,

Since there is an active relationship, please try this:

Total Amount =
CALCULATE (
    IF (
        Table2[Currency Factor] = 0,
        Table1[Amount],
        DIVIDE ( Table1[Amount], Table2[Currency Factor] )
    ),
    USERELATIONSHIP ( Table1[N], Table2[N] )
)

 Besides, why can't you choose Table1[N] to table visual? I think you should provide more detailed information about your actual tables.

Regards,

Yuliana Gu