A rule for calculating count of base elements which meet a certain criteria (measure)

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • A rule for calculating count of base elements which meet a certain criteria (measure)

    I have a question regarding rules. Our goal is to count how many project have margin over 40% and how many have the margin under 40% (this is not the actual client’s problem but a similar idea). These are called margin categories.

    The projects are divided to the project groups. So projects are base level and project groups are consolidated level.

    The rules we have used give a project (or the measure Margin_Over40_Count) a value “1” if criteria (for example margin over 40%) is met. When we select one month and several projects groups, we can see how many projects are in certain margin category in each group.

    The rules look a bit like this:
    =B:['Measure':'Margin_Over40_Count'] = IF(['Measure':'Margin'] > 0.4, 1, null)

    (The result is the same if the rule is for all elements and doesn't work if the selection is "Apply to: consolidated elements")

    The problem is that in yearly (or while using time element last three months etc) reports these calculated count values are summed up together and are that way incorrect.

    I tried to attach a picture that would easily explain the situation but didn't work... :thumbdown: Here a simple version:

    Source Code

    1. Measure Project Last 3m 201810 201811 201812
    2. Margin_Over40_Count Project Group 1 6 2 1 3
    3. Margin_Over40_Count Project 1 1 0 1 0
    4. Margin_Over40_Count Project 2 1 1 0 0
    5. Margin_Over40_Count Project 3 1 0 0 1
    6. Margin_Over40_Count Project 4 1 0 0 1
    7. Margin_Over40_Count Project 5 2 1 0 1
    8. Margin Project Group 1 50 % 47 % 30 % 55 %
    9. Margin Project 1 42 % 37 % 82 % 16 %
    10. Margin Project 2 27 % 72 % 9 % 7 %
    11. Margin Project 3 53 % 28 % 31 % 89 %
    12. Margin Project 4 47 % 31 % 16 % 83 %
    13. Margin Project 5 58 % 67 % 14 % 84 %
    Display All
    Here if we look the figures of last three months, the Project Group 1 should have 4 Projects in the margin category of "over 40 %" but instead has 6. The question we want an answer is "How many projects had a margin over 40% in last three months in each Project Group" and now we don't get it.

    Do you have some ideas how to fix this?
  • New

    Hi,

    May be you could create a rule that replace the summed up value with a maximum aggregation in case the measure is "Margin_Over40_Count"
    like this:

    ['Time':'Last 3m'] = IF(!"Measure"== "Margin_Over40_Count",MAX(['Time':'201810'],['Time':'201811'],['Time':'201812']),STET())

    Of course you would need a rule for every consolidated element of the time dimension which is not ideal...

    Maybe it gives you an idea to start with.. This should not be applied to the Project Group consolidated element so need to be adapted..