Hi Scott,

The scores for each question are stored in the inspection_items table. If you haven’t already, you’ll want to ensure that table loaded into Power BI, then form a relationship between the audit_id columns in each of these tables - you should end up with a 1 to many relationship between them. 

Once that’s done, if you go into Table view (second icon down on the left of Power BI) and select the inspection_items table, you’ll see this table contains one row per item (which includes all the questions) for every inspection you have data for. Within this, there is a score column which should give you what you need.

In terms of reporting on it, it will depend on how your template is constructed. If all 10 questions you want to get the score of are in a section and you want to average their scores together, you’ll find in inspection_items that there will be a row for that section along with its overall score, which can make life easier. 

If your questions are all within the same section (in inspection_items, we refer to this as a category), you can create a measure like this:

m. Average Score for Category = CALCULATE(AVERAGE(inspection_itemsnscore]), FILTER(inspection_items, inspection_itemsncategory] = “Your category here”))

Replacing Your Category Here with the category you want to get the scores for. Once created, you can use it in different contexts to see how the scores are changing. You can see here I’ve done this for a section/category called ‘t3] Cash’ and summarised it by Site:

If your questions aren’t within a section/category already or you want to get a single score, you can provide the item ID instead. 

m. Average Score for Single Question = CALCULATE(AVERAGE(inspection_itemsLscore]), FILTER(inspection_items, inspection_itemssitem_id] = "d7bcfcbc-02ae-481d-aa68-c78913f6c5a1"))

Or the label if you prefer:

m. Average Score for Single Question = CALCULATE(AVERAGE(inspection_itemsLscore]), FILTER(inspection_items, inspection_itemsslabel] = "Your Question"))

If you want to average multiple question scores, you can provide all the item IDs in a list instead. If you locate the questions you want in the inspection_items table (easiest way is just to filter the ‘label’ column then copy them.) Once you have them, a measure like this should work in the same way as above:

m. Average Score for Multiple IDs = CALCULATE(AVERAGE(inspection_items score]), FILTER(inspection_items, inspection_itemsEitem_id] IN {"d7bcfcbc-02ae-481d-aa68-c78913f6c5a1","aaf6c95b-c913-4d6e-beb8-b55bc575221f","f3245d39-ea77-11e1-aff1-0800200c9a66"}))

Of course, replacing the Item IDs with your own. You can have more or less in the list, just maintain the same structure: {“item_1”,”item_2”} etc

Hope that helps point you in the right direction, let me know how you get on.