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....

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi deb_power123 ,

 

To my knowledge, there is only one format available in a single column.

 

You could format it like:

=FORMAT([VALUE],"0.00%")

But the data type would be changed to Text instead.

Format ParamScore (return Text) = SWITCH([Category],
"AverageScore", CONVERT( CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),STRING),
"AveragePercentage",FORMAT( CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"0.000%"))

 

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
Helper V
4 years ago

Hi Anonymous Its a great solution but the problem will occur when we need to perform any arithematic calculations with the given measure Paramscore because it is in text format so it wont support any addition or substraction from any other measure or column....