Move From Access To SQL Server: Best Calculation options?

I am in the process of moving my Access database tables and update process onto SQL Server. I have been able to transfer the historical data and create tasks via SSIS to import the data regularly the issue is replicating the saved queries that were one in Access

For example I had a query that calculated multiple KPIs using SQl such as

SUM(Case when ThisField = 5 then 1 else 0))

However short of creating a query in SQL Server and saving it somewhere I cant seem to find a better answer.

I had considered using SSAS, that way all of these functions could be predefined however as the majority of this is simply on 1 table that proved difficult

Other users will need to access these queries, before they were stored within Access and could modify as they need but I am not so sure with SQL Server, what option would be best?

Thanks