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?
12 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
-
Many thanks for replying and explaining this has worked!
-
Glad to help .
By the way, ObeeOne's second suggestion, if applicable, is even simpler to implement and will perform better.
You can create a new custom measure that looks something like this:Sum(if([table].[value column] < X, new_value, [table].[value column]))Note that the column used is the per-aggregated column, not the measure.
So it would be [facts].[sales] and not [measures].[facts sales].
This will work if the threshold is testable and the row-level of the data OR if the grid at the hierarchy's leaves is granular enough. -
Unfortunately its not and I did try this method initially but it did not work.
-
Would you be able to elaborate more on this method, your initial method worked ok on a hand full of parts however for the whole data set it was crashing pyramid. The measure in question is calculated from aggregation of another measure which then aggregated over time using the cumulative time measure another calculated formula measure is added onto this. There is a lot of logic around this field before its placed in the grid. Is the complexity of the measure stopping this other method from working? We cannot just use a pre aggregated fact straight from the data source as more logic is needed using the data from the model.
-
many deeply nested exotic calculations could require lots of resources and time to evaluate.
Another approach which is lighter to evaluate is to avoid the hierarchy, and use the attributes directly.
So you can create the "new sales" measure, the same way, and then put the 3 attributes of the levels on the rows.
Now turn on totals as "sum" + subtotals, and you'll see the values of new sales adding up
You can add the hierarchy as a filter, to clean down the grid to the relevant items.
This does lower the viewing flexibility of the hierarchy on the rows but is much much lighter to execute:
Could this work for you report?
-
This would work, as in show the correct values for each level and is quicker, however the client wanted the hierarchy functionality to only show the level the care to view.
-
I Just had another idea as to how to approach this.
Create a single custom measure which is an extension of the New Sales calc.
The calc was if(condition, value, other value).
Now wrap that with Sum({All members of the attribute which is used as the leaf level}, if(...))
So in my case the new calculation looks like this:
Now simply use this measure alongside the hierarchy:
-
This seem to help show all parts numbers in the grid however it isn't showing the correct values, I believe this is due to the fact we have another level below part number which is transaction type. Changing the Model Attribute field to the Transaction Type does fix the values at the Part level but not at level above this as I'm guessing the aggregation is not calculating at lowest level of the hierarchy shown, which is Site > Brand > Part > Transaction Type, but just Transaction Type as a whole.
model attribute: Part Number
Model Attribute: Transaction Type
-
Yes, the model attribute inside the SUM calculation should be the lowest level of the hierarchy (assuming that the threshold condition is meant to evaluated at that level -> if the transaction type value is blow value... and then sum this up the tree).
Rolling up the values should work with this method. Maybe order of operations is somehow affecting the calculation - this could happen when crossing with custom members (as oppose to custom measures) and potentially with custom measures referring to other attributes.
Try changing the solve order of this new calc to a higher number compared to the other calculates (the default value is 0 for all calcs).
The members in the image are all the relevant members? (CR01 doesn't have any other descendants that where eliminated, besides the 00671 which is collapsed)? So the values of all members are OK except for the top 2 members which should be 1660, same as the Qty Adjusted View column? -
I created a unique field for the lowest level made up of Site, Brand, Part and Transaction Type to use in your newest calculation and this fixed the calculation for all levels.
-
-
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).