topic Re: DAX formula resulting in the currency code for which the value is highest in the fact table in DAX Commands and Tips

DAX formula resulting in the currency code for which the value is highest in the fact table

PshemekFLK — Wed, 31 May 2023 10:01:09 GMT

Hi,

I have the fact table "fct_bookings" that looks like this:

In my dimension table "dim_distributor" I would like to create two calculated columns:

Net USD - which will result in the sum of all Net USD values from fact table for currency that has the highest share (based on Net USD value) for that particular customer

Currency - currency code with the highest share (based on Net USD value)

Result would look like this:

Can you help me with DAX for columns "Net USD" and "Currency"?

Re: DAX formula resulting in the currency code for which the value is highest in the fact table

FreemanZ — Wed, 31 May 2023 09:57:14 GMT

hi PshemekFLK

what do you mean by share?

Re: DAX formula resulting in the currency code for which the value is highest in the fact table

PshemekFLK — Wed, 31 May 2023 10:03:54 GMT

Hi FreemanZ ,

I mean if one customer ordered 399 in GBP and 300 in EUR then result should show 399 in GBP as it is higher than EUR, I just edited the second table with the correct desired result. Apologies for confusion.

Re: DAX formula resulting in the currency code for which the value is highest in the fact table

FreemanZ — Wed, 31 May 2023 10:10:28 GMT

hi PshemekFLK

are the two mentioned tables related?

Re: DAX formula resulting in the currency code for which the value is highest in the fact table

PshemekFLK — Wed, 31 May 2023 10:12:21 GMT

FreemanZ , yes - one to many relationship from dim_distributor to fct_bookings by the field BT Customer No

Re: DAX formula resulting in the currency code for which the value is highest in the fact table

FreemanZ — Wed, 31 May 2023 10:29:15 GMT

hi PshemekFLK

try to create two calculated columns like:

Currency = MAXX( TOPN( 1, RELATEDTABLE(fct_bookings), CALCULATE(SUM(fct_bookings[NetUSD])) ), fct_bookings[Currency] )

NetUsD = SUMX( TOPN( 1, RELATEDTABLE(fct_bookings), CALCULATE(SUM(fct_bookings[NetUSD])) ), fct_bookings[NetUSD] )

it worked like:

Re: DAX formula resulting in the currency code for which the value is highest in the fact table

devanshi — Wed, 31 May 2023 10:51:46 GMT

1 Net USD =

VAR maxusd = CALCULATE(MAX('fct_bookings'[Net USD]),ALLEXCEPT('fct_bookings'[BT Customer No]))

RETURN

maxusd

2 ) Currency =

VAR maxcurrency = CALCULATE(MAX('fct_bookings'[Trx Currency Code]),[Net USD]))

RETURN

maxcurrency

Re: DAX formula resulting in the currency code for which the value is highest in the fact table

PshemekFLK — Wed, 31 May 2023 10:55:10 GMT

FreemanZ This works great! Many thanks! One additional question: what if I add a grouping of customers to my dimensions table like this:

What would the DAX look like for "Group Currency"?