Trying to find most efficient method for doing simple calculations based on tables with 50,000+ rows of data

Hey guys,

I ran into a problem while creating charts that use large amounts of data.

I end up with over 50,000 rows of data and the app sync time is good until I add a formula from my projects table which adds about 130 sec to the sync time.
Sum(select(Energy Data[kWh],And([Type]=“Modeled Energy”,[Project]=[_thisrow])))

I am trying to find a more efficient way to get the annual amount of energy modeled, generated and expected energy for each project. It will need to sum 365 rows out of 50,000.

Is there a better formula I could use or would it be better to include the formula in excel? I have done that before but I remember it can create problems.

I’d appreciate any advice. Thanks!

1 Like

You may wish to explore the below article for any tips on alternative approaches in handling large datasets

3 Likes

Create a separate table for projects with one row per distinct project. Calculate your sums there.

3 Likes

Thank you! I’ll take a look.

1 Like

By doing this my sync time is still around 9 seconds… is there an alternative formula to this that may be more efficient?

SUM(SELECT(Project Data[kWh],and([Project]=[_thisrow],[Type]=“Generated Energy”)))

Nope.

1 Like