Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average Sales by Week - Analytics

Hi

I have a line chart with the Year as my legend and the week of the year as my x axis.

 

I have the sales value each week going across the x axis and all is working well.  When I use the Analytics tab and select the Average I get the average sales by week of the available data points.  For example if I select the years 2020 and 2021 we are only no week 13 so I only get the weeks from the prior year (2020) and the 13 weeks of 2021 and I get my average.  That is correct.


I am trying to figure out how to make that DAX expression, since I cannot find a way to customize the data label for my Average on the Analytics tab.  Does anyone have any ideas?

 

Thanks


Dave

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    You can create this measure to calculate the average of the each week in each year:

    Average =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year]
                IN DISTINCT ( 'Table'[Year] )
                    && 'Table'[Week] IN DISTINCT ( 'Table'[Week] )
        )
    ),
    CALCULATE (
        COUNT ( 'Table'[Week] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year]
                IN DISTINCT ( 'Table'[Year] )
                    && 'Table'[Week] IN DISTINCT ( 'Table'[Week] )
        )
    )
)

    Atttached a sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    could you maybe add a picture and some data tables?

    That would make it easier to help compared to create an own example that might fit your example.

     

    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
     
  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi Anonymous ,

    You can create this measure to calculate the average of the each week in each year:

    Average =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year]
                IN DISTINCT ( 'Table'[Year] )
                    && 'Table'[Week] IN DISTINCT ( 'Table'[Week] )
        )
    ),
    CALCULATE (
        COUNT ( 'Table'[Week] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year]
                IN DISTINCT ( 'Table'[Year] )
                    && 'Table'[Week] IN DISTINCT ( 'Table'[Week] )
        )
    )
)

    Atttached a sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.