Custom table creation with monthly average values

Hi deb_power123 ,

Please use CROSSJOIN() to create a new table:

NewTable =
var _t1= DISTINCT( SELECTCOLUMNS('Table',"YearMonth",FORMAT([AuditDate],"yyyy mmmm")))
var _t2=SELECTCOLUMNS({"AverageScore","AveragePercentage"},"Category",[Value])
return CROSSJOIN(_t1,_t2)

Then create a column:

ParamScore = SWITCH([Category],"AverageScore",CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"AveragePercentage",CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])))

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

works perfectly 🙂  ...Its a little new concepts for me but I am happy to learn....