Forum Discussion
Power ParticipantCalculating Running Average for the Current Financial Year
Hello folks,
Need your help for calculating the running average for the current financial year. Below is the scenario:
I have 2 tables. 1] Date table 2] Branch Details table
I have a column with name as Branch which contains branch name. Also there is a flag which indicates whether the branch was active. I wanted to calcuate the Branch activation the explaination for which is provided below:
Branch Activation
– Average percentage of active branch from start of FY till current month
- Active Branch – Branch with 1 Policy in a month called active for that particular month
Apr - 10 off 200 branches remained active (i.e 5%)
May – 20 off 200 branches found active (10%)
June – 30 off 200 branches found active (15%)
So, for the period Apr-June or YTD June, Average Monthly Branch activation percentage would be (5+10+15)/3 = 30/3 = 10%
I am able to calculate split w.r.t. month on month (%) using quick calc functionality and by creating a simple measure named as "Branch Active"
But how would I calculate the average uptil current month in that FY.
I have tried using below references but with no success
Thanks in advance :)
2 Replies
SeanCommunity Champion
This is a Measure I use to show me the average across the months selected (which is the line in the chart)
So whatever Measure you are using to plot the individual columns use it in this formula
to get the line which represents the average across allselected months (hope this makes sense)
MEASURE Avg/Mo = DIVIDE ( CALCULATE ( [MEASURE], ALLSELECTED ( 'Calendar' ) ), CALCULATE ( DISTINCTCOUNT ( 'Calendar'[Year-Month] ), ALLSELECTED ( 'Calendar' ) ), 0 )This is the result
So regardless if I display all data ( and have to scroll on a very long chart) or narrow down with the Slicers
the line will be the average across only the selected number of months
At least thats how I understood your question
Hope this helps!
Good Luck! :smileyhappy:
