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
Regular Visitor
8 years ago

Hi Edgar,

 

This still does not solve my problem. 

This is what I put into the measure: Avg by Q = CALCULATE(sum('Retail_Quarter'[USD Net Rev])/3,ALL('CalendarMonthYear'[MonthBeginID]))

The averaged data (right table)  does not fill into all monthes on left table. e.g. right table Month 201507 average is 791,697; this number only showed up on left table for month 201507. The 201508 and 201509 do not have any number fill in. 

 

I've been hitting my head against the wall the whole morning and trying to get it solved. but still no solution. Help please. 

 

 

waltheed's avatar
waltheed
Icon for Impactful Individual rankImpactful 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
      Icon for Impactful Individual rankImpactful 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.