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

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.

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

  • 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