Thanks in advance for looking into this query.
I have broken down my query in 3 parts

1 - Details Tab = If you look at 2 matrix one is showing 750 in Nov’20 and other is breaking down the measure in Winter I and 2 and if you add them it is more than 750. How can I create a dynamic measure which does the average but does total the average on a monthly basis not averaging the monthly totals.

2 - Roster Tab = once dynamic measure is created from above I want to put them in column chart like the above chart which shows 750 in November which is currently averaging monthly totals as described in 1st point. Also the other issue is when I click on Nov 750 bottom column chart should show break down of 750 by location, which works fine when I click on December. The issue I believe is in 1st chart because it should show 954 which are average shifts for Nov Month (Winter 1 and 2). You can check that from Tab 3 - Normalise.

3 - Normalisation = Once dynamic measure is created from 1st i want to show the data in the column chart. Step 2 graph should look like Normalise tab chart. Now in Normalise tab I want to normalise the days such as Dec20 doesn’t have any Winter 1 work type and total locations were 8. To normalise it and show it in column I want to create measure that it should divide 563 by 8 and then multiply by 12 which is maximum location at the moment. I don’t want to hard code any number 8 or max location number 12.

Can we do this in power bi. I am really struggling with and any help from the experts will help me.

First of all thanks for your help. Yes I can see 1 and 2 is working. However point 3 is different to what I want in power bi, and may be I wasn’t clear enough so my apologies.
So lets take tab 3 from your file.
Feb total depots were 12 so we don’t have to normalise because that is correct figure. So half session/location was Winter 1 and other half was winter 2 which made 12 which is our total locations.
However when you take December it is only Winter 2 so to normalise it I should be doing avg roster divide by 8 and times by 12. First I don’t want to hard code 12 and I don’t want to hard code 8. I was wondering if it is possible to create a dynamic measure which captures and automatically normalise if its less than max depot.

I hope it makes sense, let me know if there is anything unclear.
Thanks for your help again

If you plea refer to Sep’21 Winter 1
There are 4 locations A,I,E,H and 3 off them are having Winter 1 13 days and E location has 18. So what average is doing avergaing over 18 days so 3 depots should be divided by 13 for average but it is dividing by 18 hence if you see the 2nd tab it is showing 886 hours
Avg from Winter 1 should be 397 but it is showing due to 318 due to this issue. Can you please have a look.

I would like to share one point of view from my side -Specifically for sheet1 -1st Question on detailed tab.
Please see if it works
When ever you select more then one month , the subtotal at month level is correct , however the grand total is not correct

We can use the below -Measure to return the correct sum of average both at subtotal and grand total level. Please reveiw and let know if it is useful
Here is the measure

Thanks Dhurbojit for your help. Thanks for highlighting the problem and yes your average dynamics resolve the issue but what I am trying to achieve is, which will resolve the issue I am having in tab 1 and 2.

Lets take September’2021 I have attached Excel spreadsheet as well as of the Roster.
Location A,I,E,H Winter 1 work type

Total Work

Total Days

Average

Power BI Total Days

Power BI Average

A

1105

13

85

18

61

I

1391

13

107

18

77

E

2034

18

113

18

113

H

1196

13

92

18

66

Total

397

318

You can see Power BI is divide all shifts by 18 not by each total work by each location days when you see the total for the month.

Also my 3rd and last tab is I would like to Normalise.
December 2020 563 Only Winter 2 shifts were ran by 8 depots. Max depot is 12 which I would like to be dynamic so any new locations is captured in the formula. So total work divide by 8 and multiply by 12 for December and So on for other months where total depots are not maximum. So If you take Feb21 it is showing 967 which is combined for 12 locations. You can see these numbers in Tab 2.

I am still not getting the requirement can you give me what should appear on number of days because as per data 18 is correct .When i check on the excel.

if change axis like show in screen shot it matches with you excel i guess or i am missing some thing here:

Thanks for your note.
YEs it does but if you add Anurag_avg_try it should be 397
if you add 85+113+92+107 = 397 subtotal is showing 318, which is what power bi is doing dividing all total work by 18 hence 318.
I hope its more clear now, and thanks for your help again.

Just to add a little to my previous post.
Also as Dhurbojit as mentioned when I pick 2 months it does show incorrect totals and average dynamic resolves the total issues however it divides each location average by incorrect number of days.
If using Average Dynamic Measure it is dividing A,I,E,H total work by 18.
Your measure is showing correctly in your pic but subtotal and total (multiple months) is not correct.

Thanks again in advance for your help.
Cheers
Manny

Normalisation issue is
Lets take tab 3 from your file.
Feb total locations were 12 so we don’t have to normalise because that is correct figure. So half session/location was Winter 1 and other half was winter 2 which made 12 which is our total locations.
However when you take December it is only Winter 2 so to normalise it I should be doing avg roster divide by 8 and times by 12. First I don’t want to hard code 12 and I don’t want to hard code 8. I was wondering if it is possible to create a dynamic measure which captures and automatically normalise if its less than max depot on a monthly basis. Result of this will bring Dec’20 average hours from 563 to 900 plus.

Yes 12 is the max location but I don’t want to hard code.
The number 8 is coming from total locations who ran Winter 1 and Winter 2 work type. I am only concerned to report Winter 1 and 2 number and normalise them accordingly.
So if you see Dec’20 it had no Winter 1 and only Winter 2. Hence 8.