Forum Discussion
Optimizing an Average measure
Hello,
I have created a measure that averages weekly item placements over the previous six weeks. (Because the same item may be placed in the same store each week, I need to capture this at the weekly level and then create an average of weekly figures so that these are not counted twice.) The measure works, it's just very slow. Could you offer any advice on how to optimize this? One note: I am using a measure for Placements right now, but could switch to a column if that enables other methods.
Context transition might be jamming you up on the [Placements] measure. In my testing, this returns the same results and is almost 3x faster.
Placements = COUNTROWS( CALCULATETABLE( VALUES(Sales[Item Name + Store Name]), Sales[Units Sold] > 0 ) )This is your measure.
This is mine
10 Replies
Hey fullcount ,
that's a good question. It can have a few reasons why it's slow. I guess it would help to show or analyze the Measure [Placements] as you use it many times. Can you post the source code of that measure or better the file if possible?
What will definitely make it a little bit faster is to put the MAX(Sales[Date]) in a variable. Like that is has to be evaluated only once and not 6 times:
Six WK Avg Weekly Placements = VAR vMaxDate = MAX(Sales[Date]) RETURN DIVIDE( CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate ,-7,DAY))+ CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -7,-7,DAY))+ CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -14,-7,DAY))+ CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -21,-7,DAY))+ CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -28,-7,DAY))+ CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -35,-7,DAY)), 6, 0 )If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicCan you give us the calculation for the [Placements] measure? It's possible that this would give you the same result depending on what that measure is calculating.
Six WK Avg Weekly Placements = DIVIDE ( CALCULATE ( [Placements], DATESINPERIOD ( Calendar_Lookup[Date], MAX ( Sales[Date] ), -42, DAY ) ), 6 )Hi all,
Thanks for the notes so far. [Placements] =
CALCULATE(SUMX(VALUES(Sales[Item Name + Store Name]),1),Sales[Units Sold]>0); a column version would be Placements = IF(Sales[Units Sold]>0,1,0)Thanks!Context transition might be jamming you up on the [Placements] measure. In my testing, this returns the same results and is almost 3x faster.
Placements = COUNTROWS( CALCULATETABLE( VALUES(Sales[Item Name + Store Name]), Sales[Units Sold] > 0 ) )This is your measure.
This is mine
Thanks, jdbuchanan71 . I wasn't able to completely reproduce the [Placements] value with the CALCULATETABLE version. Here are the results of the two measures on the same chart:
I have triple-checked the dates involved, so I'm thinking there's just a difference on what values the two measures reach. Think it's worth me switching to a Column?
Thanks!
That chart looks like you are comparing a trend line to the actual. What if you put your placements and mine side by side in a table by date?
I had also established the MaxDate as another variable and it was throwing things off, when I rewrote with my original six MAX(Calendar_Lookup[Date]) syntax, our numbers matched. Thanks so much for your help!
jdbuchanan71 Can you see any reason why the CalcTable version you suggested would affect my ability to move the dates? I'm trying to compare the last six weeks' weekly average with the weekly average from 46-51 weeks ago (LY forward rate). When I use my old [Placements] measure, it will calculate the old range correctly, but using the new CalcTable version of the measure these two produce the same outcome:
LY Forward 6W Avg Weekly Placements:=
VAR TabPlacements = COUNTROWS(CALCULATETABLE(VALUES(Sales[Item Name + Store Name]),Sales[Units Sold]>0))RETURNDIVIDE(CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-358),-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-351),-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-344),-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-337),-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-330),-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-323),-7,DAY)),6,0)TY Last 6W Avg Weekly Placements:=VAR TabPlacements = COUNTROWS(CALCULATETABLE(VALUES(Sales[Item Name + Store Name]),Sales[Units Sold]>0))RETURNDIVIDE(CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date]),-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-7,-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-14,-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-21,-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-28,-7,DAY))+CALCULATE(TabPlacements,DATESINPERIOD(Calendar_Lookup[Date],MAX(Calendar_Lookup[Date])-35,-7,DAY)),6,0)
