Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The totals are not matching the sum of the rows. I have tried using AGGR, but still not working. What I am doing wrong?
Here is the expression:
If(Sum([Budget OccSF]) > Sum(TotalSQF),
Sum(Aggr(Sum(TotalSQF),Facility,OType)),
Sum(Aggr(Sum([Budget OccSF]),Facility,OType))
)
Here is the result table. The total for Owner is not matching the sum of the rows
Store | Yesterday | Yesterday Budget | ||
Managed | 5828 | 89.82% | 2232479.93 | |
Managed | 6164 | 97.17% | 1794356.72 | |
Managed | 6165 | 92.67% | 1431386.71 | |
Managed | 6166 | 95.71% | 2156914.57 | |
Managed | 6167 | 94.83% | 1949440.98 | |
Managed | 6168 | 78.49% | 2343843.67 | |
Managed | 6169 | 94.24% | 1130630.75 | |
Managed | 6276 | 97.19% | 2227164.3 | |
Managed | 6314 | 95.07% | 1823194.16 | |
Managed | 6345 | 91.31% | 1782223.81 | |
Managed | 6481 | 93.13% | 2252497.44 | |
Managed | Total | 92.06% | 21124133.04 | |
Owned | 745 | 95.97% | 1456016.45 | |
Owned | 800 | 96.15% | 2304389.92 | |
Owned | 802 | 89.87% | 2153505.37 | |
Owned | 912 | 89.58% | 2465275 | |
Owned | Total | 92.64% | 8993565 | 8379186.74 |
92.22% | 1966887.99 |
Usually needs the aggr-wrapping to be on the outside of the expression to ensure that the total is equally to the sum of rows. Therefore I think your expression should be rather look like:
Sum(Aggr(
If(Sum([Budget OccSF]) > Sum(TotalSQF), Sum(TotalSQF), Sum([Budget OccSF]),
Facility,OType))
- Marcus
On the measure, you should be able to change the Totals function from "Auto" to "Sum" where it will do sum of rows.
It was already set to sum
Usually needs the aggr-wrapping to be on the outside of the expression to ensure that the total is equally to the sum of rows. Therefore I think your expression should be rather look like:
Sum(Aggr(
If(Sum([Budget OccSF]) > Sum(TotalSQF), Sum(TotalSQF), Sum([Budget OccSF]),
Facility,OType))
- Marcus