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

[Taylor_Felt]

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!

[Suvrutt_Gurjar]

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

help.appsheet.com

Advanced Techniques: Horizontal Scaling

Effective scaling for apps with large datasets

[Taylor_Felt]

Thank you! I’ll take a look.

[Steve]

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

[Taylor_Felt]

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”)))

[Steve]

Taylor_Felt:

is there an alternative formula to this that may be more efficient?

Nope.