Forum Discussion

fxv_anton's avatar
fxv_anton
Frequent Visitor
6 years ago
Solved

Pivot, Multiple value: Sum / Average / calculation

Hi Experts, need your help / advise on this:

Expected outcome, with correct on the KPI reach which is suppose to be "average" OR calculation based on 40% prio 2, 30%prio3, 30% prio4). 

 

From the following transform data; could you advise on what is the best way to carry out the activity? 

CC Assignment Group Prio Value Year Category
CC1 GRP1 3 - Average 200 2020-05 # of Incidents (*)
CC1 GRP1 3 - Average 210 2020-04 # of Incidents (*)
CC1 GRP1 3 - Average 180 2020-03 # of Incidents (*)
CC1 GRP1 3 - Average 168 2020-02 # of Incidents (*)
CC1 GRP1 3 - Average 182 2020-01 # of Incidents (*)
CC1 GRP1 4 - Low 37 2020-05 # of Incidents (*)
CC1 GRP1 4 - Low 61 2020-04 # of Incidents (*)
CC1 GRP1 4 - Low 47 2020-03 # of Incidents (*)
CC1 GRP1 4 - Low 46 2020-02 # of Incidents (*)
CC1 GRP1 4 - Low 47 2020-01 # of Incidents (*)
CC1 GRP1 2 - High 2 2020-03 # of Incidents (*)
CC1 GRP1 2 - High 4 2020-01 # of Incidents (*)
CC1 GRP1 2 - High 1 2020-02 # of Incidents (*)
CC1 GRP1 3 - Average 100 2020-05 KPI reached
CC1 GRP1 3 - Average 100 2020-04 KPI reached
CC1 GRP1 3 - Average 100 2020-03 KPI reached
CC1 GRP1 3 - Average 100 2020-02 KPI reached
CC1 GRP1 3 - Average 100 2020-01 KPI reached
CC1 GRP1 4 - Low 100 2020-05 KPI reached
CC1 GRP1 4 - Low 100 2020-04 KPI reached
CC1 GRP1 4 - Low 100 2020-03 KPI reached
CC1 GRP1 4 - Low 100 2020-02 KPI reached
CC1 GRP1 4 - Low 100 2020-01 KPI reached
CC1 GRP1 2 - High 100 2020-03 KPI reached
CC1 GRP1 2 - High 100 2020-02 KPI reached
CC1 GRP1 2 - High 100 2020-01 KPI reached
CC1 GRP1 2 - High 0 2020-03 # of Breaches
CC1 GRP1 2 - High 0 2020-02 # of Breaches
CC1 GRP1 2 - High 0 2020-01 # of Breaches
CC1 GRP1 4 - Low 0 2020-05 # of Breaches
CC1 GRP1 4 - Low 0 2020-04 # of Breaches
CC1 GRP1 4 - Low 0 2020-03 # of Breaches
CC1 GRP1 4 - Low 0 2020-02 # of Breaches
CC1 GRP1 4 - Low 0 2020-01 # of Breaches
CC1 GRP1 3 - Average 0 2020-05 # of Breaches
CC1 GRP1 3 - Average 0 2020-04 # of Breaches
CC1 GRP1 3 - Average 0 2020-03 # of Breaches
CC1 GRP1 3 - Average 0 2020-02 # of Breaches
CC1 GRP1 3 - Average 0 2020-01 # of Breaches

Need Help
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi fxv_anton ,

     

    Has your problem been solved?

    Please do like this.

     

    Measure = 
IF(
    MAX(Sheet4[Category]) = "KPI reached",
    IF(
        HASONEFILTER( Sheet4[Value] ),
        SUM(Sheet4[Value]),
        AVERAGE( Sheet4[Value] )
    ),
    SUM(Sheet4[Value])
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

6 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi fxv_anton,

     

    Try to do like this.

    Measure = 
IF(
    HASONEFILTER('Sales (2)'[Sale 2015]),
    SUM('Sales (2)'[Sale 2015]),
    AVERAGE('Sales (2)'[Sale 2015] )
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • fxv_anton's avatar
      fxv_anton
      Frequent Visitor
      6 years ago

      Thanks v-lionel-msft 

      I tried it out, and it work well for the KPI. 

      However, could I have mix value? by doing this, I have correct figure for KPI (if taking average), but incorrect value for number of incident and number of breaches now. 
      Thanks in advance for the support. 

      Result with Measure

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi fxv_anton ,

         

        “but incorrect value for number of incident and number of breaches now...”

        Can you mark the wrong data? I can't find the error.

        Or can you paste your sample data in a table format? I can not use the text format.

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.