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

parry2k's avatar
parry2k
Super User
4 years ago

deb_power123 you should add a calendar dimension in your model, you can follow my blog post to add  Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

 

once it is done, use month and year from calendar dimension and use following measure for average to visualize:

 

Avg Score = AVERAGE ( Table[Score] )

Avg %= AVERAGE ( Table[Percentage] )

Follow us on LinkedIn and  to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.