Mixing Iterative with Non Iterative Functions

[Anonymous]

Hey,

I have a formula which calculates, at a customer by customer level, the value of their business if new. I need to use a sumx for this to iterate down the customer table in order to check if it is a new customer within the date period selected.

Wins (Current Year) = 
var Comp1Sel = SELECTEDVALUE('Measure Select'[Measure Select])
var Comp2Sel = SELECTEDVALUE('Measure Select (Second Table)'[Measure Select]) 
var mindate = STARTOFYEAR('Date Mapping'[Month])
var maxdate = ENDOFYEAR('Date Mapping'[Month])
return

if(Comp1Sel="Prior Year",

sumx('BU Master Data',CALCULATE([Comparator 2],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate) - 
                      CALCULATE([Comparator 1],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate))

,

sumx('BU Master Data',if(CALCULATE([Comparator 1 Exists?],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate)=0,
                         CALCULATE([Comparator 2],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate),0))
                        
)
-CALCULATE([Comparator 1],'BU Master Data'[BU Parent Group]="Unknown Wins")

I also have multiple other efficiency formulas. These cannot be a SUMX as the total by each customer will not tie to the total across the business due to blended rates. 

F&B Efficiency = 'Non Core Measures: Walk & Efficiencies'[Comparator 2 Revenue (Efficiency)]*(([Comparator GP % (Efficiency)]-[Comparator 2 GP % (Efficiency)]))

I need to subtract the new business formula from my efficiency formulas, as I do not want new business included within those rates. Is this possible given one is iterative and one is not? I cannot flag if a customer is new business as a column, as it'll be based on a users selection from the date table, so needs to be dynamic.

Any help would be really appreciated

[lbendlin · Accepted solution]

I would create an entirely separate measure to cover your last scenario. Re-using measures is not good practice anyway.

[Anonymous]

Thanks! I'm very interested in why reusing measures is not good practice. I couldn't find anything to support this on Google but would love to understand why. Is it performance related at all? My model uses a sort of cascading waterfall of measures where my first measure does a lot of the generic work, looking for selected values etc and then the next level down will be more specific to that measure. I know I will have at least 10 levels in some scenarios so just wondering if that is bad news performance wise?

[lbendlin]

A measure most of the time includes some sort of context transition, new context creation or context replacement. Do that in a nested fashion and you are at risk of doing a lot of unnecessary work, losing your plot, or both. Not to mention the troubleshooting nightmare when performance goes sideways.

[Anonymous]

Thanks! Will try and do some more reading on it