Forum Discussion

brianhackett5's avatar
brianhackett5
Helper I
8 years ago
Solved

if statements

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[CONSUMAB...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago
    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago
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.