I have a collection of Data with the following columns, 1. Batch , 2. Rating, 3. Date.
Steps that need to be followed to calculate the GPA
Batch | Rating | Date |
Java | 6 | 1/01/2023 12:00:00 AM |
Java | 7 | 1/15/2023 12:00:00 AM |
Java | 6 | 1/31/2023 12:00:00 AM |
Java | 8 | 2/31/2023 12:00:00 AM |
Python | 6 | 1/15/2023 12:00:00 AM |
Python | 7 | 1/31/2023 12:00:00 AM |
Python | 8 | 2/31/2023 12:00:00 AM |
How can i do the above calculation in PowerApps.
This worked for me -
ClearCollect(
Demo123,
ForAll(
Distinct(
TestList,
Batch
),
{
Batch: Value,
Result: Average(
ForAll(
Distinct(
Filter(
TestList,
Batch = Value,
Month(Date) = Month(ThisRecord.Date)
),
Batch & "," & Month(Date) & Year(Date)
),
{
Date: ThisRecord.Value,
MonthAvg: (Sum(
Filter(
TestList,
Month(Date) & Year(Date) = Last(Split(Value,",")).Value,
Batch = First(Split(Value,",")).Value
),
Rating
) / CountRows(
Filter(
TestList,
Month(Date) & Year(Date) = Last(Split(Value,",")).Value,
Batch = First(Split(Value,",")).Value
)
))
}
),
MonthAvg
)
}
)
);
Set(GPA, Round(Average(Demo123,Result),2));
I have put together a formula but i am not getting the desired output in the collection D145 -
ClearCollect(
D145,
ForAll(
Distinct(TestList, Batch),
{
Batch: Value,
Result: Average(
ForAll(
Distinct(
Filter(
TestList,
Batch = Value,
Month(Date) = Month(ThisRecord.Date)
),
Month(Date) & Year(Date)
),
{
Date: Value,
MonthAvg: (Sum(
Filter(
TestList,
Month(Date) & Year(Date) = Value,
Batch = ThisRecord.Batch
),
Rating
) / CountRows(
Filter(
TestList,
Month(Date) & Year(Date) = Value,
Batch = ThisRecord.Batch
)
))
}
),
MonthAvg
)
}
)
);
Set(
GPA,
Average(
D145,
Result
)
);