Operaciones después de tener un promedio

[eduse07]

Hola buenos dias.

En este momento busco ayuda sobre un calculo que estoy tratando de realizar, pero no he podido obtener el resultado esperado. Estoy trabajando con datos monetarios de varios paises los cuales tienen monedas diferentes, requiero llevar estos a una moneda comun que en este caso es el dolar americano (USD), para eso me estoy apoyando en una tabla "Tasa_Cambio_GP" en la que tengo la tasa de cambio por pais para cada dia. 

Calculo el promedio de la tasa de cambio de acuerdo al periodo de tiempo que tenga filtrado y esto me divide el dato monetario de mi tabla principal, esto lo realizo mediante la siguiente medida: 

Medida

Esto me funciona siempre y cuando tenga filtrado un unico pais, como se ve en las siguientes dos imagenes: 

Pero cuando deseo consultar la informacion de 2 o mas paises a la vez la tasa promedio me arroja un dato erroneo (en este caso el promedio de las dos tasas, lo cual no tiene sentido). 

Agradezco cualquier ayuda para corregir este error, con el fin de que los datos de mi tabla principal (medida Actuals Local) la pueda dividir con la tasa de cambio que le corresponde a cada pais. 

[eduse07]

Hello everyone.

I am currently working on a project in which I have information from different countries and therefore different currencies, I want to convert them to a common currency (USD). I have a table (Tasa_Cambio_GP) that has the exchange rate per day for each of the countries that looks like this:

Company	FECHATASA	TASA
MEXICO	02/02/2023	$ 18,6
PERU	02/02/2023	$ 3,8
GUATEMALA	02/02/2023	$ 7,8
AUSTRALIA	02/02/2023	$ 1,4
COLOMBIA	02/02/2023	$ 4.639,0
NICARAGUA	02/02/2023	$ 36,3
MEXICO	01/02/2023	$ 18,7
PERU	01/02/2023	$ 3,9
GUATEMALA	01/02/2023	$ 7,8
AUSTRALIA	01/02/2023	$ 1,4
COLOMBIA	01/02/2023	$ 4.648,7
NICARAGUA	01/02/2023	$ 36,3

I am performing the next measurements to obtain the exchange rate conversion:

Sum of transactions

Actuals Local =

    SUM(Transacciones[Calculo])

Conversion of the summation

Actuals USD =

VAR average_TRM = AVERAGE(Tasa_Cambio_GP[TASA])

VAR stactic_TRM = 1

VAR variable = DIVIDE([Actuals Local], average_TRM, 0)

VAR estatica = DIVIDE([Actuals Local], stactic_TRM, 0)

VAR oficina = SELECTEDVALUE(Company[Company])

RETURN

    SWITCH(TRUE(),

        oficina = "ECUADOR", estatica,

        oficina = "EL SALVADOR", estatica,

        oficina = "UK", estatica,

        oficina = "USA", estatica,

        variable)

This last measure works when I select a single country as shown in the following images.

But in case of selecting more than one country I have problems with the average rate.

I need the Mexico "Actuals Local" to be divided by the Mexico average exchange rate and the Guatemala "Actuals Local" to be divided by the Guatemala average exchange rate, and then add these values together. The expected result should look like this.

I have tried several methods, but none of them give me the expected result. I am grateful for any help to solve this.

[v-shex-msft]

Hi @eduse07,

It sounds like a common requirement to use measure expression to calculate values with multiple aggregate levels.
For this scenario, you can refer to the Greg’s blog about measure total level calculations to add a variable table with summarize function to handle multiple aggregations in the expressions.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[eduse07]

Tranks, but I tried this method, but it didn't work. The measure continues to generate only one average instead of one per country 

[v-shex-msft]

Hi @eduse07,

For measure formulas, it not able to expand to multiple rows without add category fields.

You need to change the visual to multiple row card with correspond country as category to expand the measure formula calculations.

Regards,

Xiaoxin Sheng

[eduse07 · Accepted solution]

I was able to solve the problem by pre-calculating the average and using the "ADDCOLUMS" function.

thank you in any case for answering