I feel like there is something obvious that I am missing.
As an example, my starter data has a column for Editor, a column for "Book ID" and a "Revision Number" column. There is a row for each revision on a book.
| Editor | Book ID | Revision Number |
| Sam | 4751 | 13 |
| Jenny | 5534 | 7 |
| Lee | 4839 | 1 |
| Robin | 4027 | 0 |
| Lee | 4420 | 0 |
| Sam | 4751 | 14 |
| Jenny | 5534 | 8 |
| Lee | 4839 | 2 |
| Sam | 6678 | 0 |
| Lee | 4420 | 1 |
| Sam | 4751 | 15 |
| Jenny | 5534 | 9 |
| Lee | 4839 | 3 |
| Sam | 6678 | 1 |
| Lee | 4420 | 2 |
| Sam | 4751 | 16 |
| Jenny | 5534 | 10 |
| Lee | 4839 | 4 |
| Robin | 9878 | 0 |
| Lee | 4420 | 3 |
I want to first find the highest revision number for each book, like so:
| Editor | Book ID | Max Revision Number Per Book |
| Sam | 4751 | 16 |
| Jenny | 5534 | 10 |
| Lee | 4839 | 4 |
| Robin | 4027 | 0 |
| Lee | 4420 | 3 |
| Sam | 6678 | 1 |
| Robin | 9878 | 0 |
For my result table, I want to show the editors and their average number of revisions per book:
| Editor | Average Revisions Per Book |
| Sam | 8.5 |
| Jenny | 10 |
| Lee | 3.5 |
| Robin | 0 |
I am able to somewhat build the middle table in DAX studio like so:
EVALUATE
SUMMARIZECOLUMNS(
'Book Drafts'[Book ID]
, "Max Revision Number Per Book", MAX('Book Drafts'[Revision Number])
)
It doesn't have the editor names, but it does have the highest revision number for each book ID.
However, I need to get to the average of book drafts for each editor. I tried building this measure based off another topic I found in this forum:
VAR MaxBookRevision =
ADDCOLUMNS ( // We must create a virtual table that shows the max book revision per book ID.
VALUES( 'Book Drafts'[Book ID] ),
"@MaxBookRevision", MAX('Book Drafts'[Revision Number])
) // This returns the highest draft of all of the books for every book ID
VAR FilteredBookRevision = FILTER ( MaxBookRevision, [@MaxBookRevision] >= 0 ) // We don't necessarily want to filter out books with zero revisions. We are building this variable so AVERAGEX() has something to iterate over.
VAR Result = // Iterator required to aggregate the @MaxBookRevision column
AVERAGEX ( FilteredBookRevision, [@MaxBookRevision] )
Return
Result
Unfortunately, when I build a matrix table with Editor in Rows and put the above measure in Values, it seems to return the highest number of revisions for each editor rather than the average revision number for each editor.
What are your thoughts?
