Forum Discussion

Angry_Grisho's avatar
Angry_Grisho
Frequent Visitor
2 years ago

Dividing two values based on a condition

Hello all,

 

I think this is fairly simple but I don't know why I can't get it to work.

 

 

Currently using PowerBI and I want to calculate the Budget fulflillment % (Cumplimiento mes columm), this is calculated dividing the current sales for the month (Avance Mes) between the budget for the month (Presupuesto mes) and then the results have to be distributed between sellers (catman).

 

This is the formula I have been working on:

 

Cumplimiento Mes = DIVIDE([Venta_Bruta],CALCULATE([Presupuesto Mes],all(BI_MAESTROART[CatMan])))
 
The problem here is the ALL function wich gets all the Budgets for the CATMAN instead of the current month wich is selected.
 
Can anybody help me here and direct me to the correct function?
 
Thanks in advance.

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Angry_Grisho 

     

    Please try the following Dax:

    Cumplimiento Mes = 
DIVIDE(
    [Venta_Bruta],
    CALCULATE(
        [Presupuesto Mes],
        ALLEXCEPT(BI_MAESTROART, BI_MAESTROART[MonthColumn])  // Replace MonthColumn with your actual month column name
    )
)

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Angry_Grisho's avatar
      Angry_Grisho
      Frequent Visitor
      2 years ago

      Hello thanks for your answer and sorry for the delay.

       

      Based on your suggestion I tried the following:

       

      Cumplimiento Mes =
      DIVIDE([Venta_Bruta],
      CALCULATE(
          [Presupuesto Mes],
          ALLEXCEPT(BI_VENTAS_dic23, BI_VENTAS_dic23[Fecha].[Mes])
       
      But I have the same results where the fulfillment % for each CATMAN is not callculated correctly, only the total.