DAX code request to calculate the average sales across all the stores.

[Anonymous]

 Hi

The below chart show the sales qty of a store A12 and I would like to draw a Horizontal line on the chart to display the average sales of each week across number of stores for StoreGroup Alpha.

I have attached the sample data as well for your reference. P

lease can someone help me with the DAX code to achieve the result.

https://drive.google.com/file/d/1GAZhMuXREG0ZxzcSDbMrPUdtT79VGqDG/view?usp=sharing 

Thanks
Mahad

[johnt75]

You can get the average sales for the store group with

Avg group sales = CALCULATE( AVERAGE('Sales Table'[Qty]), ALLSELECTED('Date'), ALLSELECTED(Store_Master[StoreGroup] ) )

but you will need to place some filters on the visual to only show the weeks you are interested in, otherwise it will return a value for all the weeks in the date table

[Jihwan_Kim]

Hi,

Please check the attahced pbix file down below.

[Anonymous]

Hi Jihwan

Thanks for your quick help.

I have brought all the stores for a particular week to see the average. The code is working fine and displaying the correct average,

345/8 = 43.1

Can you also please suggest to calculate the average out of all the stores in StoreGroup Alpha.

345/12 = 28.75

Appreciate your help.

Thanks

Mahad

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Please check the attached file, that I fixed the measure.

QTY group avg fix: =
VAR _currentgroup =
    MAX ( Store_Master[StoreGroup] )
VAR _storelist =
    CALCULATETABLE (
        VALUES ( Store_Master[Store ID] ),
        FILTER ( ALL ( Store_Master ), Store_Master[StoreGroup] = _currentgroup )
    )
VAR _storeslistinagroup =
    ADDCOLUMNS (
        _storelist,
        "@qty", CALCULATE ( SUM ( 'Sales Table'[All Qty (ex Mat9)] ) )
    )
VAR _storecount =
    COUNTROWS ( _storelist )
RETURN
    SUMX ( _storeslistinagroup, [@qty] ) / _storecount

[Anonymous]

Dear Jihwan

Thank you for your help to achieve the result. You are a star.

Please can you advise/suggest me how can I learn and master in DAX from the scratch.

I'm looking to learn from 1-2-1 instead of some online recorded videos which would not give me the privilege of clarifying my doubts.

Thanks

Mahad

[amitchandak]

@Anonymous ,

Averagex(Value(Date[Week]), [Sales Measure])

or

calculate(Averagex(Value(Date[Week]), [Sales Measure]), allselected())

or

calculate(Averagex(Summarize(Sale, Sales[Store],Date[Week],"_1", [Sales Measure]), [_1]),allselected())