Forum Discussion

pvaldiviag's avatar
pvaldiviag
New Member
10 years ago

Bad calculation using IF (Matrix)

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.

Need Help

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    10 years ago

    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

     

  • Baskar's avatar
    Baskar
    Resident Rockstar
    10 years ago

    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....