Bad calculation using IF (Matrix)

[pvaldiviag]

Hello,

Im using this formula

Ventas$N =
IF([SUBS_C],
//TRUE
DIVIDE(TOTALYTD(SUM(Metricas[Monto]),Tiempo[Fecha],'Terminos Financieros'[TF_id]=25),CALCULATE(SUM(Tiempo[TipoCambio])),"ERROR"),
//FALSE
TOTALYTD(SUM(Metricas[Monto]),Tiempo[Fecha],'Terminos Financieros'[TF_id]=25))

To calculate sale amounts for different companies with different curriencies.

If the company has a different currency, I divide it by the current exchange rate, if not, then i'll just show the regular amount.

I get this result.

As you can see the totals (bold numbers) are not adding up correctly. Is the problem my formula or DAX justs doesnt like me?

Help please.

Thanks in advance.

[Baskar]

Hi Pvaldiviag,

There is no prob with your formula, this the prob on power bi Total, Its nature is calculate entire sum. 

I think in prob on power bi end....

[Vvelarde]

Hi @pvaldiviag

Probably is not the best solution.

Assuming that you have a

Calculated Column called  "Cambio de Moneda" where 1 = Applied exchange rate, 0=Don't Apply Exchange Rate.

After this create a new table:

Montos-Convertidos = summarize('Companies-Details';'Companies-Details'[COMPANY];"MONTO";if(min('Companies-Details'[CAMBIO-DE-MONEDA])=0;sum('Companies-Details'[MONTO]);DIVIDE(sum('Companies-Details'[MONTO]);3.3)))

Made a relationship between tables Companies-Details and Montos-Convertidos by field =Company

Use field Montos-Convertidos as Value in a Matrix.

Edit: Replace 3.3 with your Exchange Rate Field