Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 🙂
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!
Thanks @Sean for your reply but ultimately that does not work around for me. Let me know if there is any other approach for the same.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |