Inside of transformer I'm trying to figure out how to properly rollup an average from a daily average to a weekly, monthly, yearly average for driver loads for a given region.
I have a calculated column inside of transformer that using the formula ("Load Count" / "Driver Count") / "Work Days" and at the daily level everything works out correctly.
I then add up the 3 averages in that column and divide the sum by number of days which is 3. I then have our weekly average in the summary line which appears correct.
When I drill up to the week level we run into issues.
I am using the same calculated column but instead of rolling up and giving the same weekly average it is doing the math again on weekly level and giving us an incorrect weekly average.
When you diving 560/95 you get 5.895 when you would want the 8.591 total from the first image. I've attached an image with a query studio of the numbers being returned.
How can I properly rollup the calculated column so it displays the correct average for weekly, monthly, and yearly?
Thanks!
Jason Vickers
What is your setting for the measure on the avg ? Do you have it set to do it after Rollup?
Thanks
Charlie
Yes, inside of Transformer I have the measure set to "Rollup (tab)>Regular Timing>Default (after rollup).