Having trouble to make an average over a created column
I’m encountering an issue while calculating the average sales over the last 6 months using a custom column (Mes completo) that transforms dates into a string displaying the month and year in the format MMM yyyy. I sort this column using a date column. To obtain the average, I use an average block configured as follows:
- The list is dynamic, considering 5 members before the current one and the current member.
However, the calculation doesn’t seem to work as expected. For example, I’m trying to display the average sales per day of the last 6 months, like this:
When I validate the values, they don’t match the actual results. For instance, for October, the sum of sales for the last six months should be 713, resulting in an average of 118.3, but the value displayed in the image is 15.
Could you please help identify what might be causing this discrepancy? Let me know if you need additional details or logs to troubleshoot this issue.
Thank you in advance for your assistance.
3 replies
-
hi,
Are you wanting to display the average across six months for each day of the month? So for example, the average value of Day 1 over the six months? Please clarify.
Thanks,
Ian
-
Hi ,
OK.
Your issue in your example is that you are basing the last six months off the current member, which is defined by your slicer. That's fine, but your slicer is filtering the grid (that's what slicers do!), so you're only seeing October's numbers, not the average over the six months. what is needed is to drive the calculation, but not filter the grid.
To do this, you can use Parameters.
Create a Model Parameter that contains all the months you want the user to be able to select:
Then create the average calculation, using the "fx" function on the start of the range to give the parameter lag(5) and just the parameter itself for the end of the range (the month selected):
Now construct the grid. Put day of the month on the rows and select the calculated average measure in the columns. Because the sic month average needs a parameter to be set, it will bring the parameter selection automatically (red boxes and arrows). If you want the values of the selected month to be shown as well, bring that measure and select the parameter. It will show up as a background filter, not a slicer as there is only one element (the green boxes and arrows).
Hope that helps.
Ian