Hello All, I cannot share a PBIX but I have data in the following format:
'Instance data'
| ID | | Date | | Team | | Category | | Duration |
| 1 | | 01/01/2010 | | 1 | | X | | 5 |
| 2 | | 01/01/2010 | | 1 | | X | | 10 |
| 3 | | 01/01/2010 | | 2 | | X | | 8 |
| 4 | | 01/02/2010 | | 1 | | Y | | 6 |
| 5 | | 01/02/2010 | | 2 | | X | | 3 |
| 6 | | 01/02/2010 | | 2 | | Y | | 5 |
| 7 | | 01/02/2010 | | 3 | | Y | | 2 |
'Team data'
I have a 1 to many relationship on the Team columns.
I have the following measures for analysing category 'X' entries....
Short = CALCULATE(COUNTROWS('Instance data'), Category = "X", AND(Duration > 0, Duration <6))
Total = CALCULATE(COUNTROWS('Instance data'), Category = "X", Duration > 0)
I have then created the following measure to get the average percentage of short id's (short/total), per day, per team, in the last year.
Team Average % =
VAR _startdate = EOMONTH(TODAY(), 13) + 1
VAR _table = ADDCOLUMNS(SUMMARIZE(FILTER('Instance data', Date >= _startdate), Date, 'Team data'Team Name), "New column", [Short]/[Total])
RETURN
CALCULATE(AVERAGEX(_table, [New column]))
The above appears to be working correctly and allows me to view the averages for each team over the previous year in a table. (Year, Month, Team Name, Team Average)
| Year | | Month | | Team Name | | Team Average |
| 2010 | | 01 | | A | | 7.5 |
| 2010 | | 01 | | B | | 8 |
| 2010 | | 02 | | A | | 6 |
| 2010 | | 02 | | B | | 4 |
| 2010 | | 02 | | C | | 2 |
For the purposes of further calculations, I now want to add a further column to that table that gives me the overall average for the year. I thought this would be as simple as creating a copy of the above measure but changing the final line to something like:
CALCULATE(AVERAGEX(_table, [New column]), ALL())
But upon adding this further column, the values end up identical to those output by the Team Output % measure. I can add this to a card to get the overall average but when in a table the date and team filtering persists even with the ALL function added as per below. When instead I want the average of these values such that every row contains 5.5. (7.5+8+6+4+2)/5 = 5.5
| Year | | Month | | Team Name | | Team Average | | Overall Average |
| 2010 | | 01 | | A | | 7.5 | | 7.5 |
| 2010 | | 01 | | B | | 8 | | 8 |
| 2010 | | 02 | | A | | 6 | | 6 |
| 2010 | | 02 | | B | | 4 | | 4 |
| 2010 | | 02 | | C | | 2 | | 2 |
Hope the above is clear and I am missing something obvious.
