Assessment app - help!!!!

[MrStevoCoach]

Hi all,

I am a Power Apps novice.
I want to make my job easier by creating an app to assess children at my school. Each year group will be graded On HEAD, HEART and HANDS. each grade will be broken down to ‘gold’, ‘silver’ and ‘bronze’ in each section

My data table (excel) in columns.
Name
Gender
Class
EYFS - Head
EYFS - Heart
EYFS - Hands
KS1 - Head
KS1 - Heart
KS1 - Hands
KS2- Head
KS2 - Heart
KS2 - Hands
Swimming.

I want to analyse The data in bar or pie charts. For example how many children in ‘XX’ class met gold in hands. I am struggling to make a formula and see any data collected.

Thank you in advance

Andy

[v-siky-msft]

Hi  @MrStevoCoach ,

Please try this code, first to filter the table that Hands is Gold, and Group it by Class column, then add a new column to store the number of rows in the Grouped table and finally drop the Grouped column, so MyCol is a collection of only Class and GoldHandsNo columns to store the number of students in each class who met gold in hands

ClearCollect( MyCol, DropColumns(AddColumns(GroupBy(Filter('Table', 'YFS - Hands'= "Gold" && 'KS1 - Hands' = "Gold" && 'KS2 - Hands' = "Gold"  ), "Class", "Grouped"),"GoldHandsNo", CountRow(Grouped)), "Grouped"))

The inspiration comes from https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby#aggregate-re... 

Hope this helps.

Sik

[MrStevoCoach]

Hi Sik,

I have attached an example of my data from excel and a chart of how I would like the chart to look on the tabs. The formula you sent had an '!' 

I look forward to hearing from you!

Andy 

[v-siky-msft]

Hi @MrStevoCoach 

From your sample Data chart, It seems that you only want to calculate the total amount of gold, silver, and bronze in each of the Criteria, there is no need to classify calculations into classes.

So we can First filter all records that meet each criterion and use CountRows function to get the number of rows, then use Table function to format each value into a table.

Please try this code:

ClearCollect(MyCol, 
Table(
    {'Key Stage':"EYFS",'Criteria':"HEAD",'Gold':CountRows(Filter(Table1,'EYFS-HEAD'="Gold")),'Silver':CountRows(Filter(Table1,'EYFS-HEAD'="Silver")),'Bronze':CountRows(Filter(Table1,'EYFS-HEAD'="Bronze"))},
    {'Key Stage':"EYFS",'Criteria':"HEART",'Gold':CountRows(Filter(Table1,'EYFS-HEART'="Gold")),'Silver':CountRows(Filter(Table1,'EYFS-HEART'="Silver")),'Bronze':CountRows(Filter(Table1,'EYFS-HEART'="Bronze"))},
    {'Key Stage':"EYFS",'Criteria':"HANDS",'Gold':CountRows(Filter(Table1,'EYFS-HANDS'="Gold")),'Silver':CountRows(Filter(Table1,'EYFS-HANDS'="Silver")),'Bronze':CountRows(Filter(Table1,'EYFS-HANDS'="Bronze"))}
))

Hope this helps.
Sik

[MrStevoCoach]

Hello Sik,

Still no luck populating a Bar Chart. 

I have changed the name of my table now so hopefully I will be able to understand a bit more. (I am still a PowerApps novice).

My table is called 'DataTable' which is on Excel in my Onedrive. 

This table is on a second tab on my spreadsheet with formulas counting the data. I am not sure if this matters but helps me understand what data is being uploaded.

Key Stage	Criteria	Gold	Silver	Bronze
EYFS	Head	4	10	3
	Heart	1	4	6
	Hands	10	1	1

Many thanks

Andy 

[v-siky-msft · Accepted solution]

Hi @MrStevoCoach 

Have you tried the code above?
As you can see from the following PowerApps screenshot, The code is to generate a table like excel you want.
1. Connect Table1 to PowerApps data source
2. Add a button, Set OnSelect property as below:

ClearCollect(MyCol, 
Table(
    {'Key Stage':"EYFS",'Criteria':"HEAD",'Gold':CountRows(Filter(Table1,'EYFS-HEAD'="Gold")),'Silver':CountRows(Filter(Table1,'EYFS-HEAD'="Silver")),'Bronze':CountRows(Filter(Table1,'EYFS-HEAD'="Bronze"))},
    {'Key Stage':"EYFS",'Criteria':"HEART",'Gold':CountRows(Filter(Table1,'EYFS-HEART'="Gold")),'Silver':CountRows(Filter(Table1,'EYFS-HEART'="Silver")),'Bronze':CountRows(Filter(Table1,'EYFS-HEART'="Bronze"))},
    {'Key Stage':"EYFS",'Criteria':"HANDS",'Gold':CountRows(Filter(Table1,'EYFS-HANDS'="Gold")),'Silver':CountRows(Filter(Table1,'EYFS-HANDS'="Silver")),'Bronze':CountRows(Filter(Table1,'EYFS-HANDS'="Bronze"))}
))

3. Add a DataTable control, set Items property to MyCol and check all fields to show the table.

4. Add a Column Chart, set NumberOfSeries property to 3, and set Data property as below:

Please have a try and let me know the result.

Sik

[MrStevoCoach]

Thank you Sik,

This has worked!!

Kind Regards

Andy