Hierarchy Not Totalling Correctly When Adding Logic to a Measure Formula
I have a report example that shows values accumulating over a two-year time frame. If, at any point, the value for a specific Part/Type falls below a defined threshold, it is replaced with a static value specific to that part.
In the example below:
For the period from June to September, for Part 01692 with Type ON-HAND, the values in the Plan column have been overridden and set to 150, replacing the original monthly values:
June: 10
July: 4
August: -7
September: -13
This logic is applied using a formula within the measure:
IF current value < threshold THEN new value ELSE current value
The issue I'm encountering is that the hierarchy does not calculate this correctly at the parent levels. For example, at the Part level, it displays a total of 23, whereas it should be 150 + 13 = 163. I believe this happens because Pyramid is aggregating the values across types before comparing to the threshold, rather than applying the logic at the lowest level and then summing the results.
What’s frustrating is that when I add a separate TOTAL column and use SUM instead of AGGREGATE, the result 1684 is correct. However, the top level in the hierarchy still shows 1544.
Can this be fixed?
Is there a way to force the hierarchy to use SUM instead of AGGREGATE?
Or can the measure be adjusted to apply the threshold logic at the lowest level and then sum the results up through the hierarchy?
5 replies
-
Hi ,
The rolling up of the hierarchy values is the first mathematical operation that happens. The calculation evaluation happens second. This means that expression that you wrote is calculated on top of the base value of the item, after the children have been aggregated. So if you write Measure > X it means that CR01's Measure value is compared, not the sum of the children of CR01 for the given calculation.
You can create a customized rollup logic to change the natural order of operations:
1 - create the calculation as you wanted (where measure values > X, do A else B)
2 - create another calculation which says:
if the current item in a leaf (has no children) then use the value of the first calculation,
otherwise sum the leaf children under the current item using the value of the first calculation
It would look something like this (focus on the clothing being filtered above 30K)
Sales is the base measure.
New Sales returns 0 if the current sales value is below 30K
New Sales Rollup has the new hierarchy aggregation logic
-
this is standard behavior.
It works like this in most high-end analytic engines, like Microsoft OLAP and SAP BW. You can try and write your own roll-up measure. Another idea, if it's at the grain, is to write your logic into a custom column - this would work out of the box without issue. But it must be at the row level (often not convenient or possible).