Hello team,
Is it possible to perform operations such as sum, average etc. aggregating values by selecting multiple fields?
Example: I have the current values of all Europe for various metrics in the last weeks, I would like to show the daily ratio depending on the warehouse, the week and the metric. I suppose I should use a formula like: sum({actuals})/sumOver(sum({actuals}),[{warehouse},{weeknum},{metric}]).
Is it correct/feasible? Do you have any suggestions or something simpler I can do?
Thanks in advance
Kind regards
Hi @abbonif,
Thanks for your question in the Community and for joining the Learning Series session earlier today.
I will try to provide a solution based on your example.
In my solution I have Cities (equivalent to your warehouses), Order Dates and a Sales metric.
My calculated field, dailyRatio, is defined as
sumOver(sum(Sales),[City,truncDate('WK',{Order Date})])/sum(Sales)
This sums the Sales metric grouped by City and the date (truncated to Week level) and then divides by the daily Sales total.
I can then build a table visual as follows (highlighting London as an example):
Note - I’ve also included columns using calculated fields for the truncated date, truncDateWK and the weekly sales total per city, weeklySumPerLocation.
Let me know if this helps for your scenario.
Many Thanks,
Andrew