Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks!
Solved! Go to Solution.
@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.
@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
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, 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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |