Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PshemekFLK
Helper IV
Helper IV

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

Hi, 

 

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

PshemekFLK_0-1685525892396.png

 

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:

PshemekFLK_0-1685527262035.png

 

 

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

7 REPLIES 7
devanshi
Helper V
Helper V

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

 

PshemekFLK
Helper IV
Helper IV

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

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:

FreemanZ_1-1685528948423.png

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

PshemekFLK_0-1685530464276.png

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

PshemekFLK
Helper IV
Helper IV

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.

hi @PshemekFLK 

are the two mentioned tables related? 

FreemanZ
Super User
Super User

hi @PshemekFLK 

what do you mean by share?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.