
1. Re: dynamic / variable aggregation
Jim Dehner Oct 5, 2018 3:31 AM (in response to Werner Engelen)good morning
Little confused  you say you have a dimension that is "cumulative" are you saying that it is a calculated field that is aggregated?
if s you have 2 options to further aggregate  one is to use window_sum() in some form or the other involves nested lod's
I prefer the LOD  you would use Fixed on the original calculation that created your yes/no aggregated dimension  then you nest the lod that in a second calculation to get the sum you want
LOD's create a different virtual level in your data set but are not aggregates in themselves  they therefore can be further aggregated
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

2. Re: dynamic / variable aggregation
Werner Engelen Oct 5, 2018 4:39 AM (in response to Jim Dehner)Let me be a bit more precise :)
in the underneath possible value I translated from Dutch to English. In the attached workbook however all possible values are still in Dutch ... you get the picture :)
I have a dimension called 'KPI type'. Possible values: descriptive/number/ value ...
I have a dimension called 'cumulative?' which tells me whether I should 'sum' a certain measure or 'last' that same measure. possible values: cumulative/non cumulative.
I have a measure called 'actual (source)' which always contains a numeric value.
I have a dimension called 'actual reached?' which in the case, when I don't have a numeric value in the previous measure, tells me whether the actual is reached or not. Possible values; reached/not reached
I have a calculated measure called 'actual' which contains the following calculation:
if upper([KPI type]) = 'DESCRIPTIEF' then
if upper([actual reached?]) = 'BEHAALD' then 1
elseif upper([actual reached?]) = 'NIET BEHAALD' then 0
end
elseif [cumulatief?] = 'cumulatief' then [actual (source)]
else [actual (source)]
end
Now what I would like to add to this calculation is when the dimension called 'cumulative?' = 'cumulative,''to do a 'sum' and when the dimension called 'cumulative?' = 'non cumulative' to do a 'last' upon the measure 'actual (source'
thanks so much for your feedback.
Werner

3. Re: dynamic / variable aggregation
Jim Dehner Oct 5, 2018 5:16 AM (in response to Werner Engelen)Hi
I'm sorry  I don't have the language skills to work my way through your workbook but maybe the attached will help
your conditional statement it the post is placing a numeric value on each record in the data set  like adding another dimension (column) in the data
that dimension can be treated like any other dimension
see the attached  it is a straight forward superstore example just using category as an analogy to your calculated field
I added this formula
which will summ, take the last table value or set a value of 0 depending on the dimension value
it will return this (calculated down the table
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

multi ch0ice_v10.2.twbx 2.4 MB
