Forum Discussion

fullcount's avatar
fullcount
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    fullcount 

    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

     

     

     

     

selimovd's avatar
selimovd
Most Valuable Professional
4 years ago

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 regards
Denis