Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using IF and Calculate with Filters

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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]))
))

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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?