I have created some measures to get the average routes of our DSPs per week.
for that I created:
-
in order to get the total number of routes on daily level:
→ ifelse(sum(routes) > 0,
sumOver(sum(routes),[{route_date},{station[ds_launches]},dsp]),0) -
in order to get the average routes on week level bvased on 1
→ avgOver({actuals_average_routes_0},[{route_week},{route_year},{station[ds_launches]},dsp])
however, in my table I can see the correct average when the day column is expanded, but when I collapse them into the week column, the average gets summed up. I want a table to show the average in the week column, not per day, otherwise it will repeat itself every day of the week.
in the example below, week 13 the average is the correct one. I want this value to be showing when I collapse the week column and not show it on the date column, it does not make sense.
see for instance week 12: it is colapsed and it summs up all values (I dont even know were the “910” comes from, but point is I dont want that number). the correct value that should show is 24 which is the exact value when I expand the week and see the day columns within the week.
question is: how do I avoid the values to change when I collapse the week column?
