Calculate Average sales for a given year/week number and particular salesperson

[alexa_0028]

Hi All,

I want to calculate average sales for the given year/week/salesperson, I am not able to get the right result.
Can someone please help me?

DASX for Weekly Avg =

CALCULATE(AVERAGEX(Sales,[Sales]),

ALLEXCEPT('Calendar','Calendar'[D. Week],'Calendar'[D. Year])

,ALLEXCEPT('SalesPerson ','SalesPerson'[salesperson]))

[alexa_0028]

Hi @AlexisOlson 
Thank you again, for finding time to help me with this.
I am actually looking for something like this :

Average sales based on week/year/salesperson only .
With your updated dasx I am not getting the right result, it is showing me the same output for weekly average column.

[AlexisOlson · Accepted solution]

How about this then?

Daily Avg per Week =
CALCULATE (
    AVERAGEX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( SUM ( Sales[Sales] ) ) ),
    ALL ( 'Calendar'[D. Day of Week] )
)

[alexa_0028]

Thank you so much @AlexisOlson 
This works for me perfectly 🙂

[alexa_0028]

Hi @AlexisOlson 
Thanks for your reply but this is giving me weekly average same as sales value now and also removing blank rows.

[AlexisOlson]

Ah, I misunderstood what you meant by "weekly average". It looks like you actually want a daily average.

See if this works like you expect:

Daily Avg =
CALCULATE (
    AVERAGEX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( SUM ( Sales[Sales] ) ) ),
    ALLSELECTED ( 'Calendar' )
)

[AlexisOlson]

You need to make sure you're averaging at the right granularity. AVERAGEX( Sales, [Sales] ) iterates through every row of the Sales table and takes the average of [Sales] over all of those rows rather than taking the weekly average.

I'd try something like this to do a weekly average:

Weekly Avg =
VAR YearWeeks =
    CALCULATETABLE (
        SUMMARIZE ( 'Calendar', 'Calendar'[D. Year], 'Calendar'[D. Week] ),
        ALLSELECTED ( 'Calendar' )
    )
RETURN
    AVERAGEX ( YearWeeks, CALCULATE ( SUM ( Sales[Sales] ) ) )