Condition Formula

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.