Average of Total FTE Number by Program

[TheSAY]

Hello,

My name is Morgan Klaif and I work at the Summit Area YMCA.  Our old Manager of Digital Marketing introduced us to Power BI.  With him now gone, I have taken responsibility of the software, so I am still relatively newer to using it.

One of the reports I have recently created is a Monthly FTE report which shows childcare information.  I am trying to figure out how to go about created two extra columns that would show a running total and an average of that running total.

Here are the details:

1. There are three folders called "Finances", "Budget" and "Months".
2. Each of these folders, based on the way the data is exported from our membership system, contains 12 spreadsheets.  The spreadsheets are labeled as each month (January, February, March, etc...) containing that month's information.
3. Using a slicer for "Month Number" (1, 2, 3, etc...), I was able to create the dashboard page which changes the information based on the selected month.

Since there is a slicer, I am not sure how to go about adding in a calculation that would show the running total for that program and the average of that total for that program, up to and including the selected month of the slicer.

Here's an example of what I'm talking about:

In the below table, you can see the actual FTE for BH Before Care is 37.8.  Having "11" selected on our Month slicer, this is data from the Months folder and taken from the November spreadsheet.

I need to figure out how to add two columns next to "Actual FTE".

1. The first column would need to be the total running number of FTE for that program up to and including "11" or November.  For example, January is 45, February is 44, etc... All of the months up to and including "11" or November totals 369.8.  This column would need to show 369.8.  If I were to choose "10" or October, however, the total is only 332.
2. The second column would need to be the average of that total.  In the above example, the average for 369.8 would be 33.62 if "11" or November was selected.  The average for 332 would be 33.2 if "10" or October was selected.

I tried searching the forum before I posted this, but all the examples were based on date values.  This report doesn't use dates, rather than all text/decimals/currency, with the Month selection being text for the month names for the data spreadsheets and whole number for the month numbers.

Any help to get those two columns would be a tremendous help.

[v-chuncz-msft · Accepted solution]

@TheSAY,

You may view the code generated by Quick measure Running total and Rolling average and try using ALL instead of ALLSELECTED.