Forum Discussion
Helper IVdefault report currency and Dynamic currency conversion
Super UserHello Mous007
Your currency table is the conversion rates from any currency to EUR so we can join it to the invoice table on the currency ID and do a calculation like so to go from invoice currency to EUR.
Invoice Amount EUR = SUMX(Invoices,Invoices[Invoice Amount] * RELATED(Currencies[VALUE]))
Now that we have the EUR amount we can use a copy of the currency table to let the user select another conversion rate and take all invoices from EUR to the selected rate.
Invoice Amount Selected Currency =
VAR _ConversionRate = SELECTEDVALUE('Currencies Disconnected'[VALUE],1)
RETURN DIVIDE([Invoice Amount EUR],_ConversionRate)In this measure, if no selection is made or they select multiple "secondary conversion" currencies it will leave the amount in EUR otherwise it will convert to the selected currency. In my screen shot below I show invoice amount in original currency, EUR and AUD (I have AUD selected from the disconnected table). You can see the first and third columns match for invoices that were in AUD to begin with.
My sample file is attached for you to look at.
Helper IVhi jdbuchanan71 , thanks a lot for your support.
Your solution works perfectly for my business issue. One more question, so from now on whenever i am trying to use my payables/invoice amount in any type of calculation, i should always refer to the measure in selected currency ? or do you think i do do it in some other way ?
Thanks in advance
I will mark ur post as a solution but i want to wait for Greg_Deckler repose as i want to try out the solution he provided two.
Many thanks to you both. different approaches but i am learning from both.
- jdbuchanan71
You could use the [Invoice Amount Selected Currency] and have the report default to EUR. That would probably be easiest for your users.
- Mous007
Hi jdbuchanan71 , thank you for the clarification.
I just had another question that came to my mind while trying to build my main measures.
So based on my understanding in your scenario using this technique, if for example i had different kpi's or metrics i want to use in my model such as: Invoice amount(which we already calculated), Remaing amount, Approved amount , Paid amount etc.... , it means that for each one of these i will have to create two separate measure for each ?
Is there any method i can use to optimiye my calculation ? instead of having several measures on my measure table ?
I was thinking of creating a measure with multiple varibles such as the one used by Greg above where i can basically group both measures into one ?
Please let me know if that is the best approach or if you have any other suggestions.
Many thanks again.
- jdbuchanan71
Hello Mous007
You can do it all in 1 step, I just had two because of your request to always have EUR available.
Invoice Amount Converted = VAR _ConversionRate = SELECTEDVALUE ( 'Currencies Disconnected'[VALUE], 1 ) RETURN SUMX ( Invoices, DIVIDE ( Invoices[Invoice Amount] * RELATED ( Currencies[VALUE] ), _ConversionRate ) )You would just change the column feeding into the measure for each of your calcs: