Get the overall average of all scores of a nested JSON.

(1) Share

Report

Posted on by OC-13081034-0

Hi,

i want to calculate the average over all scores.

{
    "single_results": [
        {
            "name": "Rule1",
                    "results": [
                        {
                            "name": "Sample1",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 6
                        },
                        {
                            "name": "Sample2",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 5
                        },
                        {
                            "name": "Sample3",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 8
                        },
                        {
                            "name": "Sample4",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 9
                        }
            ]
        },
        {
            "name": "Rule2",
                    "results": [
                        {
                            "name": "Sample5",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 7
                        },
                        {
                            "name": "Sample6",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 6
                        },
                        {
                            "name": "Sample7",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 10
                        }
                    ]
        }
    ]
}

My code actually calculate the average of each rule and then the average of the average of this rules. Is it possible to calculate the average over all scores?

"Avg: "&   Average(
                ForAll(
                    ParseJSON(<JSONstring>).single_results As rec;
                    {name: Table(rec.result).Value; avgValue:Average(Table(rec.results);ThisRecord.Value.score)}
                );avgValue
             )

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Suggested answer

Daniel Bocklandt 5,099 Super User 2025 Season 2 on at

Like (2)

Report
Hey, OC-13081034-0

the easiest way to do this, is to add the table to a Gallery. This will be displayed like this :

There you just add another text label to display the average for every rule:

Average(ThisItem.results,score)

To get an Average for everything it is a bit more complicated since we've got a nested column and two columns have the same name. but this schould help you out:
Average( Ungroup( RenameColumns( Colsingle_results, name, rule ), results ), score )

Let me know if this solved your problem. If it did, please consider accepting this as solution so others can find it as well.

If it helped in any other way or form consider liking it so we can keep supporting each other.

Was this reply helpful? Yes No
scalca 243 Moderator on at

Like (1)

Report

I would suggest to use collections in order to bring this down

for example parse the single_results objects storing the score value into tables this will end up in your situation with two records
then I would loop through that collection and store the scores into a new collection and from there you could use the average function to calculate what you need

I stored the json string into a SPO list field this is where I'm getting it from and then use a variable to store the calculated average

ClearCollect(

AllScores,

ForAll(

ParseJSON(First('test Edited').jsondata).single_results,

ForAll(

ThisRecord.results,

{score: Value(ThisRecord.score)}

)

));

Clear(scoresNew);

ForAll(AllScores As rec, ForAll(rec.Value, Collect(scoresNew,{res: Value(ThisRecord.score)})));

Set(varScoresAvg, Average(scoresNew,res))

Was this reply helpful? Yes No
Verified answer

OC-13081034-0 13 on at

Like (1)

Report
I have now following solution. I customize the JSON to following structure:

[ { "name": "Ruleset A", "results": [ { "name": "abc", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 5 }, { "name": "cde", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 7 } ] }, { "name": "Ruleset B", "results": [ { "name": "efg", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 3 }, { "name": "ghi", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 7 } ] } ]

and with following code, i extract every single score and get the overall average:

Average( Ungroup( ForAll( Table(ParseJSON(ThisItem.Result)), { Subtable: ForAll( RenameColumns(Table(Value.results),Value,Subvalue), {Rulescore: Value(Subvalue.score)} ) } ), Subtable ), Rulescore )

Was this reply helpful? Yes No