Forum Discussion

siwyan_1's avatar
siwyan_1
Regular Visitor
8 years ago
Solved

Average quarter data by month

I would like to average the quarterly Data by monty (right table, on MonthId 201510, sale data 2161650/3=720550), and add this number to left table accordingly.

Final look should be something like:

 

MonthId          TotalSales

201510            1287000(566450+720550)

201511            1523640(803090+720550)

201512            1597530(876980+720550)

 

Help please! 

 

 

  • waltheed's avatar
    waltheed
    8 years ago

    I think I got it.

     

    This is the measure:

     

    Avg by Q = 
	CALCULATE(
		SUM('Quarter'[USD Net Rev])/3,
		ALLEXCEPT('FiscalYMD', 'FiscalYMD'[Year Quarter]),
		VALUES(FiscalYMD[Year Quarter] )
	)

    I also added two columns Year Month and Year Quarter to your date table:

     

    Year Month = FiscalYMD[Year] & "-" & format(FiscalYMD[Month], "00")

    Year Quarter = FiscalYMD[Year] & " Q" & FiscalYMD[Quarter]

    Then it looks like this: 

     

    Because I use VALUES of the quarters in the measure you can leave the quarters out of the table.

    Here is the link to your file.

14 Replies

  • waltheed's avatar
    waltheed
    Impactful Individual
    8 years ago

    Do you have a date dimension table?

    That would make it a bit easier. Then you can refer to quarters and months in your measures. 

    • waltheed's avatar
      waltheed
      Impactful Individual
      8 years ago

      This may be what you need:

       

      Create a new Measure:

      Avg by Q = calculate(sum(FactOnlineSales[SalesAmount])/3 , all('Date'[CalendarMonth]))

       

      And then another measure to sum the new one and your existing monthly total:

      Total = [Your Monthly Totals] + [Avg by Q]

       

       

      Would that work?