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.
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?
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)
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.
