Average and normalisation

DAX DAX Calculations
1

Planner.pbix (7.3 MB)
TEST.xlsx (133.5 KB)
Employees.xlsx (69.7 KB)
Hi All,

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.

Thanks in advance for your help.

Cheers
Manny

2

Anyone can help?

3

Hi,

For your first query are you looking the below kind of result:
image

if yes then you can use the below dax:

Anurag_Avrg_try =

Var tb=SUMMARIZE(Roster,Roster[Work Type],"@disctinctcount",DISTINCTCOUNT(Roster[Date]))

Var work_=ADDCOLUMNS(tb,"@totalwork",CALCULATE(SUM(Roster[Total Work])))

Var fy=ADDCOLUMNS(work_,"@divide",DIVIDE([@totalwork],[@disctinctcount]))

Var avg_roaster=SUM(‘Roster’[Total Work])/[Number of Days]

return

if(ISINSCOPE(Roster[Work Type]),avg_roaster,SUMX(fy,[@divide]))

Give it a try working on your other pointers.

Thanks,
Anurag

4

Hi,

I have solved your other two point also providing you the pbix and pls go through it and let me know if you need any more help .

Planner.pbix (7.3 MB)

Thanks,
Anurag

5

Hi Anurag,

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

Cheers
Manny

6

Hi Anurag,

Please see attached there is another issue.
Planner (1).pbix (7.3 MB)
TEST.xlsx (145.8 KB)

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.

Thank you
Manny

7

@Anurag thought not sure whether I have to tag you.

8

HI @mannymalhotra @Anurag

Thanks for Posting the question and reply

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

=======
AverageDynamic =
VAR workTypeMonthWithNoDays =
CALCULATETABLE (
SUMMARIZE (
Roster,
Roster[Work Type],
Roster[Month],
“@noDays”, CALCULATE ( DISTINCTCOUNT ( Roster[Date] ) )
),
ALLSELECTED ( Roster )
)
VAR totalWorkAtMonth =
ADDCOLUMNS (
workTypeMonthWithNoDays,
“@totalwork”, CALCULATE ( SUM ( Roster[Total Work] ) )
)
VAR averageAtMonthLevel =
ADDCOLUMNS ( totalWorkAtMonth, “@divide”, DIVIDE ( [@totalwork], [@noDays] ) )
VAR averageNormal =
SUM ( ‘Roster’[Total Work] ) / [Number of Days]
RETURN
IF (
ISINSCOPE ( Roster[Work Type] ),
averageNormal,
SUMX ( averageAtMonthLevel, [@divide] )
)

Refer screenshot for reference

image
image1550×549 50 KB

1 Like
9

Hi @Dhrubojit_Goswami @Anurag

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.
image

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.

Thanks for your help guys much appreciated.

Cheers
M
Planner (1).pbix (7.3 MB)
TEST.xlsx (147.1 KB)

10

Hi,

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:
image

Thanks,
Anurag

11

Hi Anurag,

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. :slight_smile:

Cheers
M

12

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

14

Hi,

Pls replace the measure with below given measure :
Anurag_Avrg_try =

Var tb=

SUMMARIZE(Roster,Roster[Work Type],Roster[Location],Roster[Month],"@disctinctcount",DISTINCTCOUNT(Roster[Date]))

Var work_=ADDCOLUMNS(tb,"@totalwork",CALCULATE(SUM(Roster[Total Work])))

Var fy=ADDCOLUMNS(work_,"@divide",DIVIDE([@totalwork],[@disctinctcount]))

VAR tot=CALCULATETABLE(fy,ALLSELECTED(Roster))

Var avg_roaster=SUM(‘Roster’[Total Work])/[Number of Days]

return

if(ISINSCOPE(Roster[Location]),avg_roaster,if(ISINSCOPE(Roster[Work Type]),SUMX(fy,[@divide]),SUMX(fy,[@divide])))

–if(ISINSCOPE(Roster[Work Type]),“YW”,if(ISINSCOPE(Roster[Work Type]),“YT”)))

you will able to get the below result:
image

Attaching the updated pbix for your refrence:
Planner (1).pbix (7.3 MB)

1 Like
15

Doing it now… Thanks Anurag. I will let you know asap. Cheers

16

Perfectly works Anurag, thank you very much for your help. Only the last normalisation bit is left, really really appreciated your help.

17

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.

Thanks for your help.
Cheers
M

18

Hi ,

I have two question about your 3rd senariou.

1)12 is the total location if any record have 12 we do not need to normalize it should be as it is.
2.)How 8 is coming here.

Thanks,
Anurag

19

Hi,

For your 3rd question use the below measure:
Anurag_Normalize =

Var tot_loaction=CALCULATE([No. of Location],All(Roster))

VAR avg_roa=[Average Roster]

VAR loc=[No. of Location]

Var tot_loc=CALCULATE(DISTINCTCOUNT(Roster[Location]),All(Roster))

Var rs=Divide(avg_roa,8)*tot_loaction

Return

if(tot_loaction=loc,[Average Roster],rs)

–Divide(avg_roa,loc)*tot_loc))

image

Attaching the pbix for your refrence:
Planner (1).pbix (7.3 MB)

20

Hi,

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.
image

Thank you
M

21

ok,

Got it pls check above provided solution i have remove 12 hardcode the max loaction i will try to remove hardcoded 8 according to your provided logic.

Thanks,
Anurag