Using IF and Calculate with Filters

[Anonymous]

I want to calculate a margin measure in terms of revenue for doing so i created grouped by tables for the level of detail of the project. however i want to be able to calculate this at business unit level ([NEGOCIO] as well hence the use of AllExcept(). Yet however when i want to use IF to determine if this margin is for business unit or supporting units for which i created a dummy measure from a column [Z2]. It appears the following error:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Below my code:

IF( [Z2] = 1, [Real/ProyAct] / calculate(SUM(BM_MargenesUPP[UPP-Ing-RealProyAct]),allexcept(BM_MargenesUPP,BM_MargenesUPP[NEGOCIO] ),IF([Z2] = 2,[Real/ProyAct] / CALCULATE(SUM(BM_MargenesGGBU[GGBU-Ing-RealProyAct])),[Real/ProyAct]/calculate(sum('BM_MargenesSG&A'[SG&A-Ing-RealProyAct])))))

Thanks in advance!

[amitchandak · Accepted solution]

@Anonymous 

Try like

IF( [Z2] = 1, [Real/ProyAct] / calculate(SUM(BM_MargenesUPP[UPP-Ing-RealProyAct]),allexcept(BM_MargenesUPP,BM_MargenesUPP[NEGOCIO] )),

IF([Z2] = 2,[Real/ProyAct] / CALCULATE(SUM(BM_MargenesGGBU[GGBU-Ing-RealProyAct])),[Real/ProyAct]/calculate(sum('BM_MargenesSG&A'[SG&A-Ing-RealProyAct]))
))

[Anonymous]

Thank you so much, i have another problem. When i do this and i see the margins it calculates the margin in terms of the whole company. For example the margin of the business unit is 50% but it appears 15% as is the margin it gets for the entire company.

When i use the true value of the first IF i get the correct values. Any thoughts of what could been happening?