if statements

[brianhackett5]

Hi all, 

I need to combine the below IF Statements into one column/meaure but I'm not having any success. 

Can anyone help me, please?

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 1 PORTABLE MAINTENANCE PARTS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE PARTS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 2 RSS SYSTEM PARTS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "KSS UNITS SERVICED"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 3 PFE ANCILLARIES",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "ANCILLARIES"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 4 FIRE REGISTER / FAD LOG BOOK",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FIRE REG BOOKS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 5 HOSE REEL / COVERS JACKETS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE COVERS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 6 SIGNAGE COMPLIANCE UNIT",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "SIGNS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 7 ELECTRICAL MAINTENANCE PARTS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD UNITS SERVICED"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 8 BATTERY REPLACEMENT",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD BATTERIES"))

[v-ljerr-msft]

Hi @brianhackett5,

As @Greg_Deckler mentioned above, it's better to use SWITCH fucntion in this scenario. 

TOTALS =
SWITCH (
    CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S],
    "KPI 1 PORTABLE MAINTENANCE PARTS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "PFE PARTS"
    ),
    "KPI 2 RSS SYSTEM PARTS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "KSS UNITS SERVICED"
    ),
    "KPI 3 PFE ANCILLARIES", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "ANCILLARIES"
    ),
    "KPI 4 FIRE REGISTER / FAD LOG BOOK", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "FIRE REG BOOKS"
    ),
    "KPI 5 HOSE REEL / COVERS JACKETS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "PFE COVERS"
    ),
    "KPI 6 SIGNAGE COMPLIANCE UNIT", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "SIGNS"
    ),
    "KPI 7 ELECTRICAL MAINTENANCE PARTS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "FAD UNITS SERVICED"
    ),
    "KPI 8 BATTERY REPLACEMENT", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "FAD BATTERIES"
    )
)

Regards

[Greg_Deckler · Accepted solution]

TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 1 PORTABLE MAINTENANCE PARTS",
                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE PARTS"),
                IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 2 RSS SYSTEM PARTS",
                             CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "KSS UNITS SERVICED"),
                             IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 3 PFE ANCILLARIES",
                                      CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "ANCILLARIES"),
                                      IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 4 FIRE REGISTER / FAD LOG BOOK",
                                                  CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FIRE REG BOOKS"),
                                                  IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 5 HOSE REEL / COVERS JACKETS",
                                                         CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE COVERS"),
                                                         IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 6 SIGNAGE COMPLIANCE UNIT",
                                                               CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "SIGNS"),
                                                               IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 7 ELECTRICAL MAINTENANCE PARTS",
                                                                        CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD UNITS SERVICED")),
                                                                                IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 8 BATTERY REPLACEMENT",
                                                                                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD BATTERIES")
                                                                                )
                                                               )
                                                   )
                                      )
                             )
                )
      )

But, again, I'd use a SWITCH statement if at all possible.