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"?
1 Net USD =
VAR maxusd = CALCULATE(MAX('fct_bookings'[Net USD]),ALLEXCEPT('fct_bookings'[BT Customer No]))
2 ) Currency =
VAR maxcurrency = CALCULATE(MAX('fct_bookings'[Trx Currency Code]),[Net USD]))
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 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"?
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.