Forum Discussion

brianhackett5's avatar
brianhackett5
Helper I
8 years ago
Solved

if statements

Hi all,    Could someone tell me how the below expression should be written so as to have a number of different IF Statements in it. When using one if statement it works perfectly but won't let me ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi brianhackett5,

     

    For your situation, I think SWITCH function will suitable for your requirement:

    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,

    Xiaoxin Sheng

     

brianhackett5's avatar
brianhackett5
Helper I
8 years ago

Thank you for your reply, I don't fully understand it unfortunately (im pretty new to Power BI).

 

I have 8 if statements which perfectly one at a time but I can't use anymore than one (i think it has something to do with the calculate function. I've posted the 8 if statements below. 

 

  • 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"))

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi brianhackett5,

 

For your situation, I think SWITCH function will suitable for your requirement:

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,

Xiaoxin Sheng