Answered by:
want to Measure formula for divide the number of Sqft by number of Days in a month using DAX query
Question

we are creating a measure to calculate the average of the sqft count for month.
Date
Sqft
NumberOfDays
10/25/2021
297
31
10/26/2021
297
31
10/27/2021
297
31
10/28/2021
297
31
10/29/2021
297
31
10/30/2021
297
31
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
We need a measure for calculating
Average of Event count per month= sum(Sqft for a month)/numberofdays in the month.
Example for Oct month : sum(1782)/31 (31 number of days in Oct
Thursday, November 12, 2015 1:58 PM
Answers

You should make sure you have a date dimension for this, rather than superfluous columns in your fact table.
Additionally, this isn't per month, this is per day. You need to define how you'd like this measure to aggregate across month boundaries for us to give you a comprehensive answer.
At the month level the measure is as simple as this:
TotalSqft:= SUM(FactTable[Sqft]) Days:= COUNTROWS(DimDate) SqftPerDay:= [TotalSqft] / [Days]
Below the month level, this will have a denominator of 7 at the week level and 1 at the day level.
Above the month level, this will count the number of days in the quarter/semester/year and use that as the denominator.GNet Group BI Consultant
 Proposed as answer by AmitTomar Friday, November 13, 2015 2:13 AM
 Marked as answer by Michael Amadi Sunday, November 29, 2015 8:56 PM
Thursday, November 12, 2015 2:28 PM 
Hi Rangareddydt,
According to your description, you need to calculated the average value for each month, right?
If you have multiple months in your data model, you can use a calculated column to get the month and then calculate the total value for each month, here is the sample DAX expression for you reference.
Month:=Month(test1113[Date])
Average:=CALCULATE(SUM(test1113[Sqft]),ALLEXCEPT(test1113,test1113[Month]))/test1113[NumberOfDays]Regards,
Charlie Liao
TechNet Community Support Proposed as answer by Michael Amadi Sunday, November 22, 2015 1:53 PM
 Marked as answer by Michael Amadi Sunday, November 29, 2015 8:57 PM
Friday, November 13, 2015 2:21 AM 
If you want a measure, I would create the Month column as above and use a formula like this:
AverageMeasure:=SUM(test113[Sqft])/AVERAGE(test113[NumberOfDays])
Put that measure in a table with [Month] and you should get the average per month because of the context filtering. The AVERAGE[NumberOfDays] will give you 31 for October since X * Y / Y = X.
 Proposed as answer by Michael Amadi Sunday, November 22, 2015 1:53 PM
 Marked as answer by Michael Amadi Sunday, November 29, 2015 8:57 PM
Sunday, November 15, 2015 3:29 PM
All replies

You should make sure you have a date dimension for this, rather than superfluous columns in your fact table.
Additionally, this isn't per month, this is per day. You need to define how you'd like this measure to aggregate across month boundaries for us to give you a comprehensive answer.
At the month level the measure is as simple as this:
TotalSqft:= SUM(FactTable[Sqft]) Days:= COUNTROWS(DimDate) SqftPerDay:= [TotalSqft] / [Days]
Below the month level, this will have a denominator of 7 at the week level and 1 at the day level.
Above the month level, this will count the number of days in the quarter/semester/year and use that as the denominator.GNet Group BI Consultant
 Proposed as answer by AmitTomar Friday, November 13, 2015 2:13 AM
 Marked as answer by Michael Amadi Sunday, November 29, 2015 8:56 PM
Thursday, November 12, 2015 2:28 PM 
And for divisions, always use DIVIDE() to avoid dividing by zero (although when counting days this would not normally happen).
So: DIVIDE([TotalSqft],[Days],BLANK())
Thursday, November 12, 2015 9:35 PMAnswerer 
Hi Rangareddydt,
According to your description, you need to calculated the average value for each month, right?
If you have multiple months in your data model, you can use a calculated column to get the month and then calculate the total value for each month, here is the sample DAX expression for you reference.
Month:=Month(test1113[Date])
Average:=CALCULATE(SUM(test1113[Sqft]),ALLEXCEPT(test1113,test1113[Month]))/test1113[NumberOfDays]Regards,
Charlie Liao
TechNet Community Support Proposed as answer by Michael Amadi Sunday, November 22, 2015 1:53 PM
 Marked as answer by Michael Amadi Sunday, November 29, 2015 8:57 PM
Friday, November 13, 2015 2:21 AM 
If you want a measure, I would create the Month column as above and use a formula like this:
AverageMeasure:=SUM(test113[Sqft])/AVERAGE(test113[NumberOfDays])
Put that measure in a table with [Month] and you should get the average per month because of the context filtering. The AVERAGE[NumberOfDays] will give you 31 for October since X * Y / Y = X.
 Proposed as answer by Michael Amadi Sunday, November 22, 2015 1:53 PM
 Marked as answer by Michael Amadi Sunday, November 29, 2015 8:57 PM
Sunday, November 15, 2015 3:29 PM