In the image you can see the dark blue values where the “Total” for the WTD Delta uses “Sum” in the totals section. The WAPE calculation which involves the WTD Delta still uses the “Default” for the “Total” calculation. How can I make sure that the totals tie off with the other values in the totals. Meaning, we should get 5861/81896.61 = 7.16%, but instead we’re getting the calculation where it takes the “Default” sum calculation for both the numerator and the denominator.
WTD Delta : same as ({[Weekly] WK-4 Cumulative Delta - Node })
Hello @gmikay, I wonder if it is struggling to aggregate each individual runningSum in the total before running the division aggregation. How is the runningSum aggregating in relation to the image you highlighted above?
Alternatively, have you tried updating the total calculation from Default to Sum for the WTD WAPE function?
RunningSum works fine, and yes I’ve tried using SUM for WTD Total aggregation but this just sums up all the row values. The issue is that the numerator is incorrect here, it uses “Default” total rather than the “Sum” total that I’ve selected in its own column, is there any work-around
Hello @gmikay, I understand that the runningSum works fine, but what are you using it for? I can’t tell from the screenshot how it impacts the values that are displayed in the row.
Is the Node field being used as the Row value and the Reporting Week field used as the column value? Also, where is the Metric Date field being used in the visual? It is difficult to provide a solution, when I can only see a portion of the pivot table.
Hello @gmikay, I think you are hitting a limitation here with the pivot table display and the calculations that are being implemented. Each row value for that day is for a runningSum value that is partitioned by that week, the corresponding Node value, and displaying a value for that single day. Providing a total for a sum of those percent values likely wouldn’t make a ton of sense anyway because the percent is related to the week (which would be table across) rather than the single day (which would be table down).
Maybe it would be better to provide Subtotals for the corresponding Node by week instead of column down on each individual day. You could also hide the Totals for the percent value but keep it for the WTD Delta field to avoid any confusion.
Hello @gmikay, since we have not heard back from you in 3 days, I will close out this topic. If you need further assistance with this issue, please post a new question in the community and include a link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!
