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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a measure with column of many measures done on different columns from different tables

 

 

A measure that calculate 

 

if A.currency = B.currency then(these conditions may be different)

mymeasure = A.measure(sum of price) * B.Exchangerate

 

How i can achieve this please help!

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Please try to use below formula, I create a summary variable table to stored total sales and correspond exchange rate for calculate:

Result =
VAR summary =
    SUMMARIZE (
        T1,
        [Currency],
        "Total Sales", SUM ( T1[Sales value] ),
        "Ex rate", LOOKUPVALUE ( T2[Exchange Rate], T2[Currency], [Currency] )
    )
RETURN
    SUMX ( summary, [Total Sales] * [Ex rate] )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

I think it will be help if you share table structures and sample data for test and coding formula .

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

hi thanks for your reply please find below table structure with sample data

 

Table 1
CurrencyDateSales value New Measure
AED01-01-2019100 Sum of sales value currency wise per month
AED02-01-2019110  
AUD02-01-2019120  
     

 

Table 2 
CurrencyExchange Rate
AED0.21
AUD0.71

 

i need

my measure = New Measure*Exchangerate (currency wise grouped)

 

i.e for AED 

new measure = 110 +100

mymeasure = new measure * Exchangerate(0.21)

Hi @Anonymous ,

Please try to use below formula, I create a summary variable table to stored total sales and correspond exchange rate for calculate:

Result =
VAR summary =
    SUMMARIZE (
        T1,
        [Currency],
        "Total Sales", SUM ( T1[Sales value] ),
        "Ex rate", LOOKUPVALUE ( T2[Exchange Rate], T2[Currency], [Currency] )
    )
RETURN
    SUMX ( summary, [Total Sales] * [Ex rate] )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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