Hopefully this is what you're looking for. Note that I convert the JSON data to XML as part of the flow so we can use XPath to calculate across the rows.
For this example, I've used the following list. Class is using the Title field (renamed to Class). Trainer is Single line of text. Survey Class and Survey Trainer are Number. Note that I added a couple of extra rows (A5) that happened last month, so won't be used in the flow.
See full flow below. I'll go into each of the actions.
Get items will retrieve all the items where the Survey Date is the current month. It uses the following expression for the Filter Query. The expressions get the start of the current month and the start of the following month and check that the Survey Date is between those dates.
SurveyDate ge '@{startOfMonth(utcNow())}' and SurveyDate lt '@{startOfMonth(addToTime(utcNow(), 1, 'month'))}'
By default, Get items will only return a maximum of 100 items. If you have a lot (or expect to have a lot) of items in your list, you will need to go into Settings, turn on Pagination, and set a Threshold larger than the number of items you would expect to have in your list over the next few years.
XML (Compose) converts the data returned from Get items to XML so we can use XPath expressions. This makes it much easier to perform calculations across rows (removes the need for loops). The expression used is:
xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))
Select is where we build the data for our table. Below are the expressions used. XPath in Power Automate is still a bit limited and doesn't include the average (avg) function, so we need to divide the sum by the count to get the average. We use formatNumber with 'N2' so our averages come back as numbers with 2 decimal places.
//From - gets the list of trainers and uses union to remove duplicates
union(xpath(outputs('XML'), '//root/value/Trainer/text()'), xpath(outputs('XML'), '//root/value/Trainer/text()'))
//Trainer
item()
//Survey Class Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer="', item(), '"]/SurveyClass/text()) div count(//root/value[Trainer="', item(), '"]/SurveyClass/text())')), 'N2')
//Survey Trainer Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer="', item(), '"]/SurveyTrainer/text()) div count(//root/value[Trainer="', item(), '"]/SurveyTrainer/text())')), 'N2')
The Select above provides us with the scores for each trainer. However, we still need to calculate the average scores across all trainers. Total (Compose) builds a single object within an array, retrieving the totals using the following expressions.
formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyClass/text()) div count(//root/value/SurveyClass/text())')), 'N2')
formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyTrainer/text()) div count(//root/value/SurveyTrainer/text())')), 'N2')
The full code that you can copy/paste into Total is below:
[
{
"Trainer": "Average Score",
"Survey Class Score": @{formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyClass/text()) div count(//root/value/SurveyClass/text())')), 'N2')},
"Survey Trainer Score": @{formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyTrainer/text()) div count(//root/value/SurveyTrainer/text())')), 'N2')}
}
]
Create HTML table uses the union of the two arrays - Select and Total. Effectively, it combines the averages for each trainer, and the total averages across trainers.
union(body('Select'), outputs('Total'))
Compose HTML table style has some CSS that will make the HTML table look nicer in the email. The CSS is below:
<style>
table {
border-collapse: collapse;
}
table td,
table th {
border: 1px solid #ddd;
padding: 6px 20px;
text-align: center;
}
table td:first-child,
table th:first-child {
text-align: left;
}
table th, tr:last-child {
background-color: #1C6EA4;
color: white;
}
</style>
Finally, Send an email uses the output from both Compose HTML table style and Create HTML table. It also uses the following expression within the Subject and Body to display the current month.
formatDateTime(utcNow(), 'MMMM')
After running the flow, we should get the following output.
----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
