Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX

Hi,

 

I have the following "table 1" with sales data of different currencies  ($ and USD):

 

 

I have a "table 2" (not related), with different conversion rates (TC) for the currencies used.

Example: 1 USD = 40 $

 

I need to create a calculated colummn, so that everytime I select a "TC" option on my report, the table 1 shows a "TOTAL USD" sales.

 

Example if TC=40, the result would be: (TOTAL MONTO USD)

 
 

 

 

I guess I must use RELATEDTABLE since my TC options are multiple. I tried the following but had no success.

 
TOTAL MONTO USD = IF('Table 1'[MONEDA ($/USD) ]="USD", 'Table 1'[MONTO (S/IVA)], 'Table 1'[MONTO (S/IVA)]/SELECTCOLUMNS(RELATEDTABLE(Table 2).
 
Any tips??
 
Thanxxx
 
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    Does Table 2 contain only one field named TC? You can create a measure as below instead of a calculated column to get USD value base on the selected TC value dynamically.

    Measure for TOTAL MONTO USD = 
VAR _tab =
    SUMMARIZE (
        'Table 1',
        'Table 1'[MONEDA ($/USD)],
        "_USD",
            IF (
                SELECTEDVALUE ( 'Table 1'[MONEDA ($/USD)] ) = "USD",
                SELECTEDVALUE ( 'Table 1'[MONTO (S/IVA)] ),
                DIVIDE (
                    SELECTEDVALUE ( 'Table 1'[MONTO (S/IVA)] ),
                    SELECTEDVALUE ( 'Table 2'[TC] ),
                    0
                )
            )
    )
RETURN
    SUMX ( _tab, [_USD] )

    If the above one is not applicable for your scenario, please provide your expected result with more details. Thank you.

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    Does Table 2 contain only one field named TC? You can create a measure as below instead of a calculated column to get USD value base on the selected TC value dynamically.

    Measure for TOTAL MONTO USD = 
VAR _tab =
    SUMMARIZE (
        'Table 1',
        'Table 1'[MONEDA ($/USD)],
        "_USD",
            IF (
                SELECTEDVALUE ( 'Table 1'[MONEDA ($/USD)] ) = "USD",
                SELECTEDVALUE ( 'Table 1'[MONTO (S/IVA)] ),
                DIVIDE (
                    SELECTEDVALUE ( 'Table 1'[MONTO (S/IVA)] ),
                    SELECTEDVALUE ( 'Table 2'[TC] ),
                    0
                )
            )
    )
RETURN
    SUMX ( _tab, [_USD] )

    If the above one is not applicable for your scenario, please provide your expected result with more details. Thank you.

    Best Regards