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 ...
  • siwyan_1's avatar
    siwyan_1
    8 years ago

    sssssssssssssss

  • 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.

  • waltheed's avatar
    waltheed
    8 years ago

    It works with me...

     

     

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?

     

    • siwyan_1's avatar
      siwyan_1
      Regular Visitor
      8 years ago

      sssssssssssssss

      • siwyan_1's avatar
        siwyan_1
        Regular Visitor
        8 years ago

         

        Sorry, this actuall not solved my problem. 

        this is what I tried:

        Avg by Q = CALCULATE(sum('Retail_Quarter'[USD Net Rev])/3,ALL('CalendarMonthYear'[MonthBeginDate].[Date]))

         

        I tried to change the ',' marked in red above to ';'  but it gives me error message saying ':' is not allowed.

         

        Help Please!