# 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

• 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 Friday, November 13, 2015 2:13 AM
• Marked as answer by 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 Sunday, November 22, 2015 1:53 PM
• Marked as answer by 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 Sunday, November 22, 2015 1:53 PM
• Marked as answer by 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 Friday, November 13, 2015 2:13 AM
• Marked as answer by 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 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 Sunday, November 22, 2015 1:53 PM
• Marked as answer by 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 Sunday, November 22, 2015 1:53 PM
• Marked as answer by Sunday, November 29, 2015 8:57 PM
Sunday, November 15, 2015 3:29 PM