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

The difference with my example is that I have a date dimension table, which links the months to quarters.

Do you have that as well? It's highly recommended. 

siwyan_1's avatar
siwyan_1
Regular Visitor
8 years ago

Hi Edgar,

Yes, I have a date dimention table. And montly sale and quarter sale tables are refering to the date dimention table. But still the averaged quarterly data still only fill in one month of that quater instead of all monthes. 

Please advise.

  • waltheed's avatar
    waltheed
    Impactful Individual
    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
    Impactful Individual
    8 years ago

    Can you share your pbix file?

  • siwyan_1's avatar
    siwyan_1
    Regular Visitor
    8 years ago

    Hi,

     

    I don't know how to upload a file here. This is the link to download the file: 

    https://fere.me/yaup/jbiqwq/

    thank you for your help.

  • siwyan_1's avatar
    siwyan_1
    Regular Visitor
    8 years ago

    It works perfect without any additional dimentions. After adding in the 'platform' dimention, it cracked again.  

    Any idea? Thank you so much for your help. 

  • waltheed's avatar
    waltheed
    Impactful Individual
    8 years ago

    It works with me...

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello,

     

     

    i exactly need this but not able to get it.. can you help me out ? share me the sample pbix file if possible