Hi,
One more question Winter1 and winter2 both going to have same No. of loaction or it can be diffrent.
I mean winter1 and winter2 both going to have 8 right.
Thanks,
Anurag
Hi,
Thanks for your help again. Almost there buddy 
if you see below pic
Dec has been normalised correctly but Feb’2021 is normalising as well. so For Feb it should be left unchanged 401+566 and the subtotals are not adding up as well.
Thanks so much buddy.
Cheers
M
Hi,
Pls replace the measure to remove hardcode value:
Anurag_Normalize =
Var tot_loaction=CALCULATE([No. of Location],All(Roster))
Var tot_loaction_W2=CALCULATE([No. of Location],All(Roster),Roster[Work Type]=“winter2”)
VAR avg_roa=[Average Roster]
VAR loc=[No. of Location]
Var tot_loc=CALCULATE(DISTINCTCOUNT(Roster[Location]),All(Roster))
Var rs=Divide(avg_roa,tot_loaction_W2)*tot_loaction
Return
–tot_loaction_W2
if(tot_loaction=loc,[Average Roster],rs)
–Divide(avg_roa,loc)*tot_loc))
ok got your point working on it.
Winter 1 will always be from AIEH location and Winter 2 will always be from other 8 location. and it will always be same every time.
Only thing which will be different is in AIEH that is number of days it may be slightly different which you have captured in your latest avg measure. 13 vs 18 in Sep’21 which you created the Avg measure.
Winter 1 will be 4 and Winter 2 will be 8 so total 12.
So Dec no Winter 1 only Winter 2 so 8 out 12 and will be normalising them. Just to give you a little more info that in Dec there will some other work type from other 4 locations which I don’t want to include and hence I want to normalise them based on 8 location data.
Thank you
M
Hi,
Pls try the below measure does it serve your purpose let me know:
Anurag_Normalize =
Var tot_loaction=CALCULATE([No. of Location],All(Roster))
Var tot_loaction_W2=CALCULATE([No. of Location],All(Roster),Roster[Work Type]=“winter2”)
VAR avg_roa=[Average Roster]
VAR loc=[No. of Location]
Var tot_loc=CALCULATE(DISTINCTCOUNT(Roster[Location]),All(Roster))
VAR loc_remove=if(ISBLANK([Number of Days]),BLANK(),CALCULATE([No. of Location],ALLSELECTED(Roster[Work Type])))
–Var stepchk=if(ISINSCOPE(EOM[End of Month]),if(tot_loaction=loc,“Y”,“N”))
Var rs=Divide(avg_roa,tot_loaction_W2)*tot_loaction
Return
–if(ISINSCOPE(EOM[End of Month]),if(tot_loaction=loc,“Y”,“N”)
–tot_loaction_W2
–loc_remove
if(tot_loaction=loc,avg_roa,if(loc_remove=tot_loaction,avg_roa,rs))
–Divide(avg_roa,loc)*tot_loc)))
Attaching latest file for your refrence:
Planner (1).pbix (7.3 MB)
Hi,
No this is not correct your previous normalisation formula was correct for Dec-21 as it was dividing by number of location and then multiply by max location 12.
Feb-21 already had combined 12 locations data it means it should be as same as average roster column which 401+566 = 967
So this measure is showing incorrect Dec-20 and Feb-21 numbers and subtotal and total numbers are not adding up.
Thank you Anurag, I am really stuck in this one.
pls check again i upadted it sorry for the mistake.
I am not sure whether we can exchange contact details, just so I explain it a lil more better.
No worries buddy, you are really helping me so I am very thankful to you. What do you want me to check, can you please attach the file please.
Yes very close mate… Just sub total and total in Feb '21 it is currently showing 665.85 but it should be 401+565 = 967 and Total should be 844.5+967=1811.5
One last thing I want to check with you this measure will work if there are month which has only Winter 1 shifts as well and no winter 2?
Reason I am asking you I can see Var for W2.
Thank you
Please disregard above qs regarding whether it will work. It is working great. Just subtotal and total issue
grt to hear that working on total and sub total part
1 Like
There was a post with pic and disappeared. I couldn’t see it so not sure if you have shared something?
HI,
Is it good:
Magical 
. Can you please share the pbix
Just replace the below dax:
Anurag_Normalize =
Var tot_loaction=CALCULATE([No. of Location],All(Roster))
Var tot_loaction_W2=CALCULATE([No. of Location],All(Roster),Roster[Work Type]=“winter2”)
VAR avg_roa=[Average Roster]
VAR loc=[No. of Location]
Var tot_loc=CALCULATE(DISTINCTCOUNT(Roster[Location]),All(Roster))
VAR loc_remove=if(ISBLANK([Number of Days]),BLANK(),CALCULATE([No. of Location],ALLSELECTED(Roster[Work Type])))
–Var stepchk=if(ISINSCOPE(EOM[End of Month]),if(tot_loaction=loc,“Y”,“N”))
Var rs=Divide(avg_roa,tot_loaction_W2)*tot_loaction
-----for total and subtotal----
Var tb=SUMMARIZE((Roster),Roster[Work Type] ,Roster[Month],"@no_of_loaction",([No. of Location]),"@avg",([Average Roster]))
Var sub3=ADDCOLUMNS(tb,“tot_location”,
Var month1=[Month]
var rs=SUMX(FILTER(tb,[Month]=month1),[@no_of_loaction])
return
rs)
Var work_2=ADDCOLUMNS(sub3,"@val",if([tot_location]=tot_loaction,[@avg],Divide([@avg],tot_loaction_W2)*tot_loaction))
Return
–if(ISINSCOPE(EOM[End of Month]),if(tot_loaction=loc,“Y”,“N”)
–tot_loaction_W2
–loc_remove
if(ISINSCOPE(Roster[Work Type]),if(tot_loaction=loc,avg_roa,if(loc_remove=tot_loaction,avg_roa,rs)),SUMX(work_2,[@val]))
–Divide(avg_roa,loc)*tot_loc))))
pbix for your refrence:
Planner (1).pbix (7.3 MB)
Buddy I can’t thank you enough… looking very good and it is working perfectly. Just one minor issue.
September 2021 Winter 1 should be 397 not 318, this is the same issue which you created a measure for tab 1 and 2 to resolve the different number of days between AIEH hence difference from 397 to 318.
