Pivot, Multiple value: Sum / Average / calculation

[fxv_anton]

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

[v-lionel-msft]

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]

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]

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.

[fxv_anton]

Thanks for the continuous support @v-lionel-msft .

Correction: when I mention incorrect, I meant that is not the expected result.

When we calculate KPI, we could take average or using percentage calculation.

But when we talk about total number tickets and breaches, we should "sum it up". 

Hence, could we have mixed calculation in that aspects?

Data in Table: 

CCAssignment GroupPrioValueYearCategory

CC1	GRP1	3 - Average	200	Friday, May 1, 2020	# of Incidents (*)
CC1	GRP1	3 - Average	210	Wednesday, April 1, 2020	# of Incidents (*)
CC1	GRP1	3 - Average	180	Sunday, March 1, 2020	# of Incidents (*)
CC1	GRP1	3 - Average	168	Saturday, February 1, 2020	# of Incidents (*)
CC1	GRP1	3 - Average	182	Wednesday, January 1, 2020	# of Incidents (*)
CC1	GRP1	4 - Low	37	Friday, May 1, 2020	# of Incidents (*)
CC1	GRP1	4 - Low	61	Wednesday, April 1, 2020	# of Incidents (*)
CC1	GRP1	4 - Low	47	Sunday, March 1, 2020	# of Incidents (*)
CC1	GRP1	4 - Low	46	Saturday, February 1, 2020	# of Incidents (*)
CC1	GRP1	4 - Low	47	Wednesday, January 1, 2020	# of Incidents (*)
CC1	GRP1	2 - High	2	Sunday, March 1, 2020	# of Incidents (*)
CC1	GRP1	2 - High	4	Wednesday, January 1, 2020	# of Incidents (*)
CC1	GRP1	2 - High	1	Saturday, February 1, 2020	# of Incidents (*)
CC1	GRP1	3 - Average	100	Friday, May 1, 2020	KPI reached
CC1	GRP1	3 - Average	100	Wednesday, April 1, 2020	KPI reached
CC1	GRP1	3 - Average	100	Sunday, March 1, 2020	KPI reached
CC1	GRP1	3 - Average	100	Saturday, February 1, 2020	KPI reached
CC1	GRP1	3 - Average	100	Wednesday, January 1, 2020	KPI reached
CC1	GRP1	4 - Low	100	Friday, May 1, 2020	KPI reached
CC1	GRP1	4 - Low	100	Wednesday, April 1, 2020	KPI reached
CC1	GRP1	4 - Low	100	Sunday, March 1, 2020	KPI reached
CC1	GRP1	4 - Low	100	Saturday, February 1, 2020	KPI reached
CC1	GRP1	4 - Low	100	Wednesday, January 1, 2020	KPI reached
CC1	GRP1	2 - High	100	Sunday, March 1, 2020	KPI reached
CC1	GRP1	2 - High	100	Saturday, February 1, 2020	KPI reached
CC1	GRP1	2 - High	100	Wednesday, January 1, 2020	KPI reached
CC1	GRP1	2 - High	0	Sunday, March 1, 2020	# of Breaches
CC1	GRP1	2 - High	0	Saturday, February 1, 2020	# of Breaches
CC1	GRP1	2 - High	0	Wednesday, January 1, 2020	# of Breaches
CC1	GRP1	4 - Low	0	Friday, May 1, 2020	# of Breaches
CC1	GRP1	4 - Low	0	Wednesday, April 1, 2020	# of Breaches
CC1	GRP1	4 - Low	0	Sunday, March 1, 2020	# of Breaches
CC1	GRP1	4 - Low	0	Saturday, February 1, 2020	# of Breaches
CC1	GRP1	4 - Low	0	Wednesday, January 1, 2020	# of Breaches
CC1	GRP1	3 - Average	0	Friday, May 1, 2020	# of Breaches
CC1	GRP1	3 - Average	0	Wednesday, April 1, 2020	# of Breaches
CC1	GRP1	3 - Average	0	Sunday, March 1, 2020	# of Breaches
CC1	GRP1	3 - Average	0	Saturday, February 1, 2020	# of Breaches
CC1	GRP1	3 - Average	0	Wednesday, January 1, 2020	# of Breaches

[v-lionel-msft · Accepted solution]

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.

[fxv_anton]

Hi @v-lionel-msft  could you help to explain the logic behind this Max?