Sum a Measure Across 2 Layers of Categorisation, Multiple Column Headers

mjholland

In your formula, it analysis on Category level only, it will not take the higher categories filter context, which means on grand total level, it will never calculate greater tha 6 since you only have 6 categories. 

For your requirement, you should use a summarize table to have your calculation group on higher categories (SS, MC). You can try formula like below; 

KPI Achieved =
SUMX (
    SUMMARIZE (
        HighCategories,
        HighCategories[HighCategories],
        "KpiCount", SUMX (
            Category,
            IF (
                Category[Category] = "Beverages",
                IF ( [%Avg] >= 1, 1, 0 ),
                IF (
                    Category[Category] = "Confectionery Homestock",
                    IF ( [%Avg] >= 1, 1, 0 ),
                    IF (
                        Category[Category] = "Confectionery Impulse",
                        IF ( [%Avg] >= 1, 1, 0 ),
                        IF (
                            Category[Category] = "Food",
                            IF ( [%Avg] >= 1, 1, 0 ),
                            IF (
                                Category[Category] = "Purina",
                                IF ( [%TGT] >= 1, 1, 0 ),
                                IF ( Category[Category] = "Waters", IF ( [% TGT] >= 1, 1, 0 ), BLANK () )
                            )
                        )
                    )
                )
            )
        )
    ),
    [KpiCount]
)

Regards,