Latest Enterprise DNA Initiatives


Dax Query for Avg

Hello Everyone,

I need Dax query, where I can get Avg based on main category rather than subcategory and then divide by total number of months, based on month filter.

Data is simple, I have main product called product 1 and it have several subcategories and its sales by month, so I trying to get an avg sales of product 1 based on month Filter.

E.g. if user select Mar as month and the avg will be calculated total sales on product1 / 9 month (year starts from Jul) but if user select Feb then product 1 / 8 months

In my model I have got two tables main data and months table. Below is the link for excel based data.

[Data]
(https://drive.google.com/open?id=1W0XCMxtIoSmSqWascygJ-FQdcm4ooQ5E)

Any help would be appreciated it.

Thanks.

Hi asif082,
Just for clarification; are you looking at having the the calculation on sales to show average for more than one month calculation, even if it’s just a month that was selected?

1 Like

Hi asif082. Here’s a quick possible solution that works off of the number of fiscal months selected. Hope it helps. Greg
TEST DAX Query for Average.pbix (86.6 KB)

1 Like

.
Hi @asif082,

So I hope I understood your requirement correctly. You want to Average the number of Sales by the number of Fiscal Months but excluding any filtering coming from subcategory…

Now I’ve added an extended Dates tabel to this model which includes Fiscal Periods, making this easy.
First I calculated the total sales for all categories, bringing back the context for the month in calendar:

Total number of Sales all cat = 
CALCULATE(
    SUM( 'Sample'[Number of Sales] ),
    ALLEXCEPT('Sample', 'Sample'[Category] ),
    VALUES( Dates[MonthInCalendar] )
)

Next to calculated the average:

Avg number of Sales all cat = 
IF( NOT( ISBLANK( [Total number of Sales all cat] )),
    COALESCE(
        DIVIDE( SUMX( VALUES( Dates[MonthInCalendar] ), [Cum Number of sales all cat] ), SELECTEDVALUE( Dates[Fiscal Period] )),
        AVERAGEX( VALUES(Dates[MonthInCalendar]), [Total number of Sales all cat] )
    )
)

So you can now select a Month in Year and/or Sub Category and the results will remain unchanged for the all cat measures.

Here is my sample file: eDNA - Dax Query for Avg.pbix (110.8 KB)
I hope this is helpful.

1 Like

Thanks Melissa you made my day, can’t thank enough, really appreciate it.

Thanks Greg, really appreciate the help and effort.