Forum Discussion

deb_power123's avatar
deb_power123
Helper V
4 years ago
Solved

Custom table creation with monthly average values

Hi All,   I have the below input source table with Audit Date,Score,SchoolName and PercentageStudents columns and  table name as Table. I need to find the average of score and percentageStudents pe...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

  • deb_power123's avatar
    deb_power123
    4 years ago

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

deb_power123's avatar
deb_power123
Helper V
4 years ago

parry2k Its because I have huge database table [many are unrelated so I need to sum them or gather the data together), you can say a data ware house with all tables.I need these data so that I can use them in another homepage of my visualization.

 

please take a look at the output , you can related what i mean above..