Forum Discussion

hoopaz68's avatar
hoopaz68
New Member
1 year ago
Solved

Dax Help

Good Afternoon.  I'm fairly new to using DAX so bear with me.    I'm trying to use DAX to calculate invoice amounts in different currencies.  I have two example tables below in my PBI desktop.  These tables are joined by the cruno field in the data model.  The correct rate to apply to the inv_amt_usd is a function of the cruno field and the currency code (which a user would select from a slicer with the currency codes).  I want to add a measure that would calculate the different currency invoice amount for each invoice so I can add the information in the invoice table to a table visual along with the correct billed amount given the selected currency.  Any Ideas?  

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi hoopaz68 ,

     

    In the data you provided, the cruno field does not have a unique identifier and cannot be used as a condition for joining tables.
    But based on your requirement, you can refer to the following formula:

    SelectedCurrencyRate =
CALCULATE (
    MAX ( 'Currency_Table'[rate] ),
    FILTER (
        'Currency_Table',
        'Currency_Table'[cruno] = SELECTEDVALUE ( 'Invoice Table'[cruno] )
            && 'Currency_Table'[currency] = SELECTEDVALUE ( 'Currency_Table'[currency] )
    )
)

    ConvertedInvoiceAmount = 
SUMX(
    'Invoice Table',
    'Invoice Table'[inv_amt_usd] * [SelectedCurrencyRate]
)

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share data in a format that can be pasted in an MS Excel file and show the expected result very clearly.

    • hoopaz68's avatar
      hoopaz68
      New Member
      1 year ago

      Here is the data for the tables and expected results:

       

      Currency_Table
      		       
      row_unocrunodatecurrencyrate   
      11009/1/2024USD1   
      21009/1/2024GBP1.5161   
      31009/1/2024EUR1.8946   
      41019/2/2024USD1   
      51019/2/2024GBP1.5475   
      61019/2/2024EUR1.9116   
      71029/3/2024USD1   
      81029/3/2024GBP1.5421   
      91029/3/2024EUR1.9177   
              
      Invoice Table
      		       
      row_unocrunoinv_numinv_dateinv_amt_usd   
      110040019/1/20245000   
      210040029/1/20248700   
      310140039/2/20242300   
      410240049/3/20241500   
      510240059/3/202410000   
              
      Results       
      row_unocrunoinv_numinv_dateinv_amt_usdcurrencyrateinv_amt_converted
      110040019/1/20245,000.00USD1.00005,000.00
      110040019/1/20245,000.00GBP1.51617,580.50
      110040019/1/20245,000.00EUR1.89469,473.00
      210040029/1/20248,700.00USD1.00008,700.00
      210040029/1/20248,700.00GBP1.516113,190.07
      210040029/1/20248,700.00EUR1.894616,483.02
      310140039/2/20242,300.00USD1.00002,300.00
      310140039/2/20242,300.00GBP1.54753,559.25
      310140039/2/20242,300.00EUR1.91164,396.68
      410240049/3/20241,500.00USD1.00001,500.00
      410240049/3/20241,500.00GBP1.54212,313.15
      410240049/3/20241,500.00EUR1.91772,876.55
      510240059/3/202410,000.00USD1.000010,000.00
      510240059/3/202410,000.00GBP1.542115,421.00
      510240059/3/202410,000.00EUR1.917719,177.00
              

       

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi hoopaz68 

     

    This is just based on the image  provided. Haven't tested as no workable data was provided.

     

    USD Converted Measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            invoice_table,
            invoice_table[cruno],
            invoice_table[inv_num],
            invoice_table[inv_date],
            invoice_table[inv_amt_usd]
        ),
        "@Converted",
            CALCULATE (
                Currency_Table[rate],
                FILTER (
                    Currency_Table,
                    Currency_Table[cruno] = [cruno]
                        && Currency_Table[inv_date] = [inv_date]
                )
            ) * [inv_amt_usd]
    ),
    [@Converted]
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi hoopaz68 ,

     

    Did danextian reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

     

    Best regards,

    Adamk Kong

    • hoopaz68's avatar
      hoopaz68
      New Member
      1 year ago

      Not quite.  I shared data and expected results for this for danextian.  Thanks.

  • hoopaz68's avatar
    hoopaz68
    New Member
    1 year ago

    Thank you for this but this didn't quite work.  Here is the data for the tables and expected results:

     

    Currency_Table
    		       
    row_unocrunodatecurrencyrate   
    11009/1/2024USD1   
    21009/1/2024GBP1.5161   
    31009/1/2024EUR1.8946   
    41019/2/2024USD1   
    51019/2/2024GBP1.5475   
    61019/2/2024EUR1.9116   
    71029/3/2024USD1   
    81029/3/2024GBP1.5421   
    91029/3/2024EUR1.9177   
            
    Invoice Table
    		       
    row_unocrunoinv_numinv_dateinv_amt_usd   
    110040019/1/20245000   
    210040029/1/20248700   
    310140039/2/20242300   
    410240049/3/20241500   
    510240059/3/202410000   
            
    Results       
    row_unocrunoinv_numinv_dateinv_amt_usdcurrencyrateinv_amt_converted
    110040019/1/20245,000.00USD1.00005,000.00
    110040019/1/20245,000.00GBP1.51617,580.50
    110040019/1/20245,000.00EUR1.89469,473.00
    210040029/1/20248,700.00USD1.00008,700.00
    210040029/1/20248,700.00GBP1.516113,190.07
    210040029/1/20248,700.00EUR1.894616,483.02
    310140039/2/20242,300.00USD1.00002,300.00
    310140039/2/20242,300.00GBP1.54753,559.25
    310140039/2/20242,300.00EUR1.91164,396.68
    410240049/3/20241,500.00USD1.00001,500.00
    410240049/3/20241,500.00GBP1.54212,313.15
    410240049/3/20241,500.00EUR1.91772,876.55
    510240059/3/202410,000.00USD1.000010,000.00
    510240059/3/202410,000.00GBP1.542115,421.00
    510240059/3/202410,000.00EUR1.917719,177.00
            

     

  • hoopaz68's avatar
    hoopaz68
    New Member
    1 year ago

    Thank you for this help.  This didn't quite work.  Here is the data for the tables and expected results:

     

    Currency_Table
    		       
    row_unocrunodatecurrencyrate   
    11009/1/2024USD1   
    21009/1/2024GBP1.5161   
    31009/1/2024EUR1.8946   
    41019/2/2024USD1   
    51019/2/2024GBP1.5475   
    61019/2/2024EUR1.9116   
    71029/3/2024USD1   
    81029/3/2024GBP1.5421   
    91029/3/2024EUR1.9177   
            
    Invoice Table
    		       
    row_unocrunoinv_numinv_dateinv_amt_usd   
    110040019/1/20245000   
    210040029/1/20248700   
    310140039/2/20242300   
    410240049/3/20241500   
    510240059/3/202410000   
            
    Results       
    row_unocrunoinv_numinv_dateinv_amt_usdcurrencyrateinv_amt_converted
    110040019/1/20245,000.00USD1.00005,000.00
    110040019/1/20245,000.00GBP1.51617,580.50
    110040019/1/20245,000.00EUR1.89469,473.00
    210040029/1/20248,700.00USD1.00008,700.00
    210040029/1/20248,700.00GBP1.516113,190.07
    210040029/1/20248,700.00EUR1.894616,483.02
    310140039/2/20242,300.00USD1.00002,300.00
    310140039/2/20242,300.00GBP1.54753,559.25
    310140039/2/20242,300.00EUR1.91164,396.68
    410240049/3/20241,500.00USD1.00001,500.00
    410240049/3/20241,500.00GBP1.54212,313.15
    410240049/3/20241,500.00EUR1.91772,876.55
    510240059/3/202410,000.00USD1.000010,000.00
    510240059/3/202410,000.00GBP1.542115,421.00
    510240059/3/202410,000.00EUR1.917719,177.00
            

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi hoopaz68 ,

     

    In the data you provided, the cruno field does not have a unique identifier and cannot be used as a condition for joining tables.
    But based on your requirement, you can refer to the following formula:

    SelectedCurrencyRate =
CALCULATE (
    MAX ( 'Currency_Table'[rate] ),
    FILTER (
        'Currency_Table',
        'Currency_Table'[cruno] = SELECTEDVALUE ( 'Invoice Table'[cruno] )
            && 'Currency_Table'[currency] = SELECTEDVALUE ( 'Currency_Table'[currency] )
    )
)

    ConvertedInvoiceAmount = 
SUMX(
    'Invoice Table',
    'Invoice Table'[inv_amt_usd] * [SelectedCurrencyRate]
)

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.