cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 cycle 333: (4 + 6 + 8)/3 and so on.

 

Untitled.jpg

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

4 REPLIES 4
v-haibl-msft
Microsoft
Microsoft

@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] )
)

Moving Average in Matrix visual_1.jpg

 

Best Regards,

Herbert

Anonymous
Not applicable

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?

 

Untitled.jpg

Anonymous
Not applicable

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

 

View solution in original post

@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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors