Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
I would create an entirely separate measure to cover your last scenario. Re-using measures is not good practice anyway.
I would create an entirely separate measure to cover your last scenario. Re-using measures is not good practice anyway.
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?
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.
Thanks! Will try and do some more reading on it
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |