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

 

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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Not sure I follow exacly, maybe you could knock up a mock table to show what you currently have and what the expected result would be with the additional column? 

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