dax formula for sales in different currencies

[ezepbi]

Hi, i want to create a formula that calculates the sales amount in ARS$ or in USD based on what I've selected in a filter.
But I'm not very good with calculate yet.

I have a sales table connected with a calendar table and a dolar price table connected 1 to 1 with the calendar table (because i have only one dollar price per day); this tables has 3 columns (fecha, precio_compra, precio_venta)

so i was working with this formula

Total_Ventas_Seleccionado =

VAR total_ventas_pesos = SUM(ventas_detalle[Venta])

RETURN

IF(

    SELECTEDVALUE('Seleccion Moneda'[Moneda]) = "Dólares",

    DIVIDE(

        total_ventas_pesos,

        RELATED(cotizacion_dolar_blue[precio_venta])

    ),

    total_ventas_pesos

)

But it's not working, the related line is in red dots and it says the parameter is of the wrong type.
I suppose i could create a relationship directly between the price dollar table and the sales table but i rather use the link with calendar since i have multiples tables with sales and buys that have the price in ARS so I would rather create a good formula.

[suparnababu8]

Hi @ezepbi 

Try with this  

Total_Ventas_Seleccionado = 
SWITCH(
        SELECTEDVALUE('Seleccion Moneda'[Moneda]),
        "Dólares", SUMX(ventas_detalle,ventas_detalle[Venta] * RELATED(cotizacion_dolar_blue[precio_venta]),
        "ARS$",  SUM(ventas_detalle[Venta])
        )

Let me know if it works.

[ezepbi]

You did it you genius!
well almost, here's what i did

Total_Ventas_Seleccionado =

var ventas_pesos = sum(ventas_detalle[venta])

return

SWITCH(

        SELECTEDVALUE('Seleccion Moneda'[Moneda]),

        "Pesos",  ventas_pesos,

        "Dólares", SUMX(ventas_detalle,ventas_detalle[Venta] / RELATED(cotizacion_dolar_blue[precio_venta])

        ))

i had to divide the sales with the dollar price because the "cotizacion_dolar_blue" table has the price in pesos of 1 USD for every single day

[Anonymous]

Hi @ezepbi ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

[Ritaf1983]

Hi @ezepbi 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly