Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Condition Formula

Does anyone know how I can embed the following formula 100(sum(KPI1)/sum(KPI1_COUNT))* as a new column. With the sum being based on each unique "Applicant_Company" and "Work_Area" and each month (See...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    According to my understanding , you want to calculate SUM(KPI1)/SUM(KPI1_COUNT) based on three categories,right?

     

    You could use EARLIER() or ALLEXCEPT() function shown below:

    Column =
CALCULATE (
    SUM ( 'Table'[KPI1] ) / SUM ( 'Table'[KPI1_COUNT] ),
    FILTER (
        'Table',
        'Table'[Applicant_Company] = EARLIER ( 'Table'[Applicant_Company] )
            && 'Table'[Work_Area] = EARLIER ( 'Table'[Work_Area] )
            && 'Table'[Date].[MonthNo] = EARLIER ( 'Table'[Date].[MonthNo] )
    )
)
    Column 2 =
CALCULATE (
    SUM ( 'Table'[KPI1] ) / SUM ( 'Table'[KPI1_COUNT] ),
    ALLEXCEPT (
        'Table',
        'Table'[Applicant_Company],
        'Table'[Work_Area],
        'Table'[Date].[MonthNo]
    )
)

    Here is the final output :

     

    Please take a look at the pbix file here.

     

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

According to my understanding , you want to calculate SUM(KPI1)/SUM(KPI1_COUNT) based on three categories,right?

 

You could use EARLIER() or ALLEXCEPT() function shown below:

Column =
CALCULATE (
    SUM ( 'Table'[KPI1] ) / SUM ( 'Table'[KPI1_COUNT] ),
    FILTER (
        'Table',
        'Table'[Applicant_Company] = EARLIER ( 'Table'[Applicant_Company] )
            && 'Table'[Work_Area] = EARLIER ( 'Table'[Work_Area] )
            && 'Table'[Date].[MonthNo] = EARLIER ( 'Table'[Date].[MonthNo] )
    )
)
Column 2 =
CALCULATE (
    SUM ( 'Table'[KPI1] ) / SUM ( 'Table'[KPI1_COUNT] ),
    ALLEXCEPT (
        'Table',
        'Table'[Applicant_Company],
        'Table'[Work_Area],
        'Table'[Date].[MonthNo]
    )
)

Here is the final output :

 

Please take a look at the pbix file here.

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi thats great thanks! can I ask, if both the KPI1 and KPI_COUNT columns are 0, do you know how within the formula I can put a IF clause which will make the calculation revert to 100?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      The DIVIDE() function was designed to automatically handle division by zero cases.

       

      See the differences between DIVIDE() and / here.

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