Condition Formula

[Anonymous]

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)

[v-eqin-msft · Accepted solution]

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]

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?

[v-eqin-msft]

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.

[Anonymous]

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?