Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Moving Average in Matrix visual

Hi Everyone, How do I make a moving average per Cycle (each 15 days in the month) and Channel with the Matrix visual. So, for the Channel E-COMMERCE, in the cycle 332, my average is (4 + 6)/2. In ...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    v-haibl-msft, sincere thanks. I coudn't download the file, maybe because of the Company's firewall.

     

    The dataset example was not good. I missed to say that one Store sells products between cycles and each Store has a Channel. The average was counting the number of rows by Channel.

    It was my fault to understand how the values are aggregated.

    I changed the Dax to:

     

     

    Moving Avg = 
CALCULATE (
    DIVIDE((sum(Plan1[VALUE]));DISTINCTCOUNT(Plan1[CYCLE]));
    FILTER (ALLSELECTED(Plan1); Plan1[CYCLE] <= MAX (Plan1[CYCLE]));
	VALUES(Plan1[CHANNEL])
)

    Now everything is fine.

     

    Thank you again. 

     

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Anonymous

 

You can use following DAX measure if you have a dataset like below.

Moving Avg = 
CALCULATE (
    AVERAGE ( Table1[VALUE] ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[CYCLE] <= MAX ( Table1[CYCLE] ) ),
    VALUES ( Table1[CHANNEL] )
)

 

Best Regards,

Herbert

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Thanks v-haibl-msft.

    It's all the same as you did except the measure and the column's order. I replaced the formula and it's still wrong. 

    Is there something I missed?

     

    • v-haibl-msft's avatar
      v-haibl-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      Anonymous

       

      From your screenshot, it seems that the measure is same as mine. I’ve uploaded my PBIX file here and you can take a look at it to see if there is any difference between our source table or something else.

       

      Best Regards,

      Herbert

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      v-haibl-msft, sincere thanks. I coudn't download the file, maybe because of the Company's firewall.

       

      The dataset example was not good. I missed to say that one Store sells products between cycles and each Store has a Channel. The average was counting the number of rows by Channel.

      It was my fault to understand how the values are aggregated.

      I changed the Dax to:

       

       

      Moving Avg = 
CALCULATE (
    DIVIDE((sum(Plan1[VALUE]));DISTINCTCOUNT(Plan1[CYCLE]));
    FILTER (ALLSELECTED(Plan1); Plan1[CYCLE] <= MAX (Plan1[CYCLE]));
	VALUES(Plan1[CHANNEL])
)

      Now everything is fine.

       

      Thank you again.