Avg and Max across multiple excel in sharepoint

[pasqualino]

Hello all,

I have a sharepoint which is being added 1 csv file per day. The files contains 1 server per row, with fields like the serverName, awsregion (just US east or Europe as values)-->example below

servername	AWS region	os type	db type
fellow	europe	linux	oracle
master	europe	windows	sql
brother	amer	windows	oracle

I need to present a dashboard in table format showing something like the following. Need something that changes according to the Month (and maybe year) selected as Filter. Did I say I have no clue?

Thanks for your help

Month	AVG (per selected month)	MAX (selected month)
os type linux
os type windows
db type oracle
db type sql
# all categories servers

[pasqualino]

hello @Akash_Varuna as I have 5000 rows per day, do you think it is a valid solution in the longterm? how feasible it is to manage > 1M lines csv?

[v-pagayam-msft]

Hi @pasqualino ,
Thank you @Akash_Varuna for the insightful responses!

Thank you for the follow-up! Handling 5,000 rows per day which may exceed 1 million rows annually is feasible in Power BI, provided the data ingestion and modeling are optimized. As @Akash_Varuna pointed, ,we recommend using Power Automate to consolidate these files into a structured format with a “Date” column added for filtering. This may then be connected to Power BI for efficient querying and aggregation. For improved scalability and performance, consider storing the data in a Dataflow Gen2 or Lakehouse if you are using Microsoft Fabric. Power BI is designed to handle large datasets when best practices are followed.

Hope this resolve your query.If so, give us kudos and consider accepting it as solution.

Regards,
Pallavi G.

[Akash_Varuna · Accepted solution]

Hi @pasqualino Use Power Automate to merge daily CSV files from SharePoint into a single data source with a "Date" column. Connect this data source to Power BI and use DAX measures to calculate the average and max values filtered by the selected month and year. Add slicers for Month and Year to filter dynamically. Finally, use a table visual to display os type, db type, and total servers with the calculated metrics.