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 Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors