Forum Discussion

kbarc24's avatar
kbarc24
Regular Visitor
2 years ago
Solved

Average Volume Week YTD by Store

Hi there, 

I'm stuck trying to come up with a way to calculate the following:
"The average weekly volume (count of rows) since the beginning of the year (YTD) OR since the first date with rows broken down by store."

That's a bit wordy so let me include a quick table. 

Week123456
Store A101617201823
Store B  57915
Store C    910

 

Ideally what we'd see is that for Store A, at week 6, our measure would produce ((10+16+17+20+18+23)/6) = 17.3. For Store B; 9. And Store C: 9.5

In a perfect world that average would be rolling, so I could look at week 5 and get 16.2, 7, and 9 respectively. 

I've been able to make it work for cases where there is data going back right to the start of the year, but I can't figure out a way to write the calculation that specifies an average only if the weeks have data.


Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, kbarc24 

    Based on your information, I create a sample table:

     

    Then create measures and try the following DAX expression:

    Rolling Average Volume A = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store A] <> BLANK()
    ),
    'Table'[Store A]
)
    Rolling Average Volume B = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store B] <> BLANK()
    ),
    'Table'[Store B]
)
    Rolling Average Volume C = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store C] <> BLANK()
    ),
    'Table'[Store C]
)

    Here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Shravan133's avatar
    Shravan133
    Super User
    2 years ago

    To calculate the average weekly volume (count of rows) since the beginning of the year (YTD) or since the first date with rows broken down by store, you can create a measure in DAX that takes into account only the weeks where there is data.

    1. Create a Measure for Cumulative Sum of Rows

    First, create a measure to calculate the cumulative sum of rows for each store up to the current week.

    Cumulative Rows =

    CALCULATE(

        SUM('Table'[Rows]),

        FILTER(

            ALL('Table'),

            'Table'[Store] = MAX('Table'[Store]) &&

            'Table'[Week] <= MAX('Table'[Week])

        )

    )

    1. Create a Measure for the Count of Weeks with Data

    Next, create a measure to count the number of weeks that have data for each store up to the current week.

    Weeks with Data =

    CALCULATE(

        COUNTROWS('Table'),

        FILTER(

            ALL('Table'),

            'Table'[Store] = MAX('Table'[Store]) &&

            'Table'[Week] <= MAX('Table'[Week]) &&

            'Table'[Rows] > 0

        )

    )

    1. Create the Rolling Average Measure

    Finally, create the rolling average measure by dividing the cumulative sum by the count of weeks with data.

    Rolling Average =

    DIVIDE(

        [Cumulative Rows],

        [Weeks with Data]

    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, kbarc24 

    Based on your information, I create a sample table:

     

    Then create measures and try the following DAX expression:

    Rolling Average Volume A = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store A] <> BLANK()
    ),
    'Table'[Store A]
)
    Rolling Average Volume B = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store B] <> BLANK()
    ),
    'Table'[Store B]
)
    Rolling Average Volume C = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store C] <> BLANK()
    ),
    'Table'[Store C]
)

    Here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.