How to calculate individual averages for different sections in same table

[binayjethwa]

Hi,

I have the following table and requirement is to have the % of final values are summed up and average is taken separately.

I need to show below table as the average of individual KPIgroup

KPIGroup	Average
Resilience	86%
Sector	80%

For example to get resilliance as 86% i need to calculate individual sum of resliliance for different KPI type and take sum of final% column.

I have below values for resiliance for KPI type as BL KPI  and 87.50% for BL Focus Area for same resliliance.

 To arrive to final average i will take sum of all below 4 values which is 84.29% and we have 87.50 for BL focus Area 

Final average is average(84.29+87.50/2) =  86%

Resilience	BL KPI	18.46%
Resilience	BL KPI	7.50%
Resilience	BL KPI	20.59%
Resilience	BL KPI	19.64%
Resilience	BL KPI	18.10%
Resilience	BL Focus Area	87.50%

BL	KPIGroup	KPIType	KPI	Level	Q1 Target	Q1 Actual	Q1 Variance	Weight	Variance_Test	Final%
Generation	Sector	BL KPI	Activate	5	0.25	0.2	-20.00%	100.00%	80.00%	80.00%
Generation	Sector	BL Focus Area	Generationeration	4	0.25	0.2	-20.00%	100.00%	80.00%	80.00%
Generation	Resilience	BL KPI	Repair Materials	5	65	60	-7.69%	20.00%	92.31%	18.46%
Generation	Resilience	BL KPI	Spare Parts Management	5	40	15	-62.50%	20.00%	37.50%	7.50%
Generation	Resilience	BL KPI	(SC)	5	91.4	94.1	2.95%	20.00%	102.95%	20.59%
Generation	Resilience	BL KPI	(CC)	5	93.4	91.7	-1.82%	20.00%	98.18%	19.64%
Generation	Resilience	BL KPI	(ST)	5	92.5	83.7	-9.51%	20.00%	90.49%	18.10%
Generation	Resilience	BL Focus Area	Operation Excellence	4	0.24	0.21	-12.50%	100.00%	87.50%	87.50%

Please let me know how can we achieve this , Thank you very much in advance.

[Anonymous]

Hi @binayjethwa ,

Please try to add a new column with below dax formula:

Avg =
VAR tmp =
    SUMMARIZE ( 'Table', [KPIType], "Sum_%", SUM ( 'Table'[Final%] ) )
VAR val =
    SUMX ( tmp, [Sum_%] )
VAR ctn =
    COUNTROWS ( tmp )
RETURN
    val / ctn

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[binayjethwa]

But all the values are same in the columns, i need a separate table having two columns. KPI type and KPI group are needed to be considered.

KPIGroup	Average
Resilience	86%
Sector	80%

 To arrive to final average i will take sum of all below 4 values which is 84.29% and we have 87.50 for BL focus Area 

Final average is average(84.29+87.50/2) =  86%

Resilience	BL KPI	18.46%
Resilience	BL KPI	7.50%
Resilience	BL KPI	20.59%
Resilience	BL KPI	19.64%
Resilience	BL KPI	18.10%
Resilience	BL Focus Area	87.50%