locked
want to Measure formula for divide the number of Sqft by number of Days in a month using DAX query RRS feed

  • 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 Amit-Tomar 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 Amit-Tomar 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 PM
    Answerer
  • 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