# Power BI -Proportion of overall criteria * Average

#### lbanham

##### Board Regular
Good Afternoon

I need help to work out how to show the proportion of days taken per category. I have posted how this looks in Excel however I am struggling to create a Measure in Power BI to replicate it.

My data is in one large table, called Sheet 1!
The example below: first 2 columns are actual (dummy) data which is SUM totalled and Averaged at the bottom. The third/forth column is how it would be calculated on a line by line basis.
The right hand columns shows how it would roll up on a Terms basis - so Z130 contributed 1.69 days to the overall average of 9.97

I wasn't sure whether to create the row level Contribution as a Calculated field first, then use a measure to summarise or if that would slow the model down (its C145k rows)

Any help and guidance would be appreciated.

 Terms Days Contribution Formula Terms Days Contribution ZR00 7​ 0.24138​ A2/\$A\$31*\$A\$32 ZR00 30​ 1.03 ZR00 8​ 0.27586​ ZR30 82​ 2.83 ZR00 8​ 0.27586​ ZI45 128​ 4.41 ZR00 7​ 0.24138​ ZI30 49​ 1.69 ZR30 8​ 0.27586​ 289.00 9.97 ZR30 7​ 0.24138​ ZR30 7​ 0.24138​ ZR30 8​ 0.27586​ ZR30 8​ 0.27586​ ZR30 7​ 0.24138​ ZR30 15​ 0.51724​ ZR30 7​ 0.24138​ ZR30 7​ 0.24138​ ZR30 8​ 0.27586​ ZI45 7​ 0.24138​ ZI45 7​ 0.24138​ ZI45 8​ 0.27586​ ZI45 7​ 0.24138​ ZI45 8​ 0.27586​ ZI45 7​ 0.24138​ ZI45 6​ 0.20690​ ZI45 8​ 0.27586​ ZI45 3​ 0.10345​ ZI45 7​ 0.24138​ ZI45 8​ 0.27586​ ZI45 7​ 0.24138​ ZI45 8​ 0.27586​ ZI45 37​ 1.27586​ ZI30 49​ 1.68966​ Total 289​ 9.97​ Average 9.97

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### arunsjain

##### Board Regular
Hi,

3rd Code Line (#"Added Custom") will add a column for calculation for each row as you need for each row. Refer to Screenshot as outcome.

Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Terms", type text}, {"Days", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Contribution", each [Days]/List.Sum(#"Changed Type"[Days]) * List.Average(#"Changed Type"[Days])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Contribution", type number}})
in
#"Changed Type1"

I hope it will help

#### Attachments

• Outcome.JPG
74.4 KB · Views: 12

#### lbanham

##### Board Regular
Thank you that works well, I think it was the formatting issues that I hadn't recognised.

Replies
1
Views
148
Replies
0
Views
162
Replies
1
Views
2K
Replies
8
Views
3K
Replies
0
Views
750

1,182,195
Messages
5,934,214
Members
436,935
Latest member
Fergjosh97

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back