Calculate Totals for columns based on weightage

[fahadqadir3]

Need to present Total, total should be sum of column value, divided by 100 minus (subtract) weightage of blank value. for example in case of December 2023. For petrol value is blank and the weightage is 10%. In this total should be the SUM of all values of December 23, divided by (100-10).

For example for December 23 = (31+0+11+21+10+5) divided by (100-10), this 10 is coming from weightage of blank value.

for December 23 = (31+0+11+21+10+5) / (100-10)*100 = 78/90*100 = 86.6%.

Similarly, (weightage of petrol 10, weightage of water 25)

For Feb 24 = (31+0+12+0+10+5) / (100-10-25)*100 = 58/65*100 = 89%.

For March 24 = (30+10+13+21+7+0) / (100-5)*100 = 81/95*100 = 85%.

Please help thank you.

Power BI File attached one drive link.

Task.pbix

https://1drv.ms/u/s!AjITNjmTPlzbhwn86qB8P_toJlpV?e=OD2Fnh

https://drive.google.com/file/d/1bpamlszR6AEu-5hnIJiIFFVXA6XGt6eF/view?usp=sharing

[fahadqadir3 · Accepted solution]

@amitchandak  thank you for the response but I'm Not able to get required(correct) total. please review the attached screenshot.

For december the total value 78 should be divided by (100 - weightage of blank KPI Measure). In this case for Petrol the value is blank no value( row/task) for this month and KPI weightage is 10. thats why (100-10) is used as denominator.

December 23 = 

for December 23 = (31+0+11+21+10+5) / (100-10)*100 = 78/90*100 = 86.6%.

Similarly for Feb 2 values are blank for petrol and water, (weightage of petrol 10, weightage of water 25)

For Feb 24 = (31+0+12+0+10+5) / (100-10-25) = 58/65*100 = 89%.

and "You need to switch grand total with measure like one below, Measure is measure for % used as of now". I'm not able to understand this statement how can i do that ?. Please help. thanks a lot.

[Ashish_Mathur]

Hi,

Share the Dataaa.xlsx file.

[fahadqadir3]

Dataaa.xlsx

@Ashish_Mathur  attached.

[Ashish_Mathur · Accepted solution]

Hi,

See if this works

[fahadqadir3 · Accepted solution]

@amitchandak  thank you for the response but I'm Not able to get required(correct) total. please review the attached screenshot.

For december the total value 78 should be divided by (100 - weightage of blank KPI Measure). In this case for Petrol the value is blank no value( row/task) for this month and KPI weightage is 10. thats why (100-10) is used as denominator.

December 23 = 

for December 23 = (31+0+11+21+10+5) / (100-10)*100 = 78/90*100 = 86.6%.

Similarly for Feb 2 values are blank for petrol and water, (weightage of petrol 10, weightage of water 25)

For Feb 24 = (31+0+12+0+10+5) / (100-10-25) = 58/65*100 = 89%.

and "You need to switch grand total with measure like one below, Measure is measure for % used as of now". I'm not able to understand this statement how can i do that ?. Please help. thanks a lot.

[amitchandak]

@fahadqadir3 , You need to switch grand total with measure like one below, Measure is measure for % used as of now

if( isinscope(Table[Sr#]), [Measure],
divide(
Sumx(summarize(Table, Table[Sr#], Table[Business Meausre KPI], Table[Weight in role], "_1", [Measure]), [_1])
,1- Sumx(summarize(Table, Table[Sr#], Table[Business Meausre KPI], Table[Weight in role], "_1", [Measure]), if(isblank([_1]),[Weight in role])) )
)

[fahadqadir3]

@amitchandak 

For december the total value 78 should be divided by (100 - weightage of blank KPI Measure). In this case for Petrol the value is blank no value( row/task) for this month and KPI weightage is 10. thats why (100-10) is used as denominator.

December 23 = 

for December 23 = (31+0+11+21+10+5) / (100-10)*100 = 78/90*100 = 86.6%.

Similarly for Feb 2 values are blank for petrol and water, (weightage of petrol 10, weightage of water 25)

For Feb 24 = (31+0+12+0+10+5) / (100-10-25) = 58/65*100 = 89%.

and "You need to switch grand total with measure like one below, Measure is measure for % used as of now". I'm not able to understand this statement how can i do that ?. Please help. thanks a lot.