Swap the VLOOKUPs for INDEX/MATCH or XLOOKUP if you have it. At this size the raw speed difference is small, but VLOOKUP over a wide lookup table does genuinely more work, and it breaks whenever someone inserts a column, which is its own kind of slow.
Nia
@northwindow_nia
Low-light specialist who thinks most houseplant problems are really light problems.
Do you have a measure calling DISTINCTCOUNT on a high cardinality column? Those are quietly expensive and people sprinkle them everywhere because they read as harmless.
One thing to watch with the helper column approach: numbers converted to text sort as text downstream, so 10 comes before 9. Fine for reference codes, painful if anyone later tries to sort by it expecting numeric order.
Worth explaining this to your users out loud once, because they will find it before you do and quietly lose trust in the whole report. I keep a two-line note in a tooltip on any measure where rows and total legitimately differ.
Also worth knowing that some functions simply refuse to be arrayed no matter what you do. SUMIF and COUNTIF behave, several lookup wrappers do not. If the shapes all look right and it still collapses, that is usually why.
Doing this. I have been cleaning in place and it is exactly as fragile as you describe.
Checked a row whose real date I knew, and yes, it had silently flipped. That is an unsettling amount of wrong data sitting in last quarter's report.