Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Advance Aggregation / Sum with rounding

Hello Expert, 

Here is what we are trying to do and completely lost the thought process while getting the formula. 

Step 3 will be based on the variance. If the variance is 1, applies to one acc, if 2, two acc, with max 10

Appreciate your help in advance. 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    According to the results of your picture, I grouped the data in ActualValue.

    So the actual total value of this group is 54(10.7*5). How to explain the "AAA-005" has two diffenrent values if you need to group the data in AccType.

    I am sorry that I ignored the "10" . Here is the new DAX:

    Step4 =
VAR _rank =
    RANKX (
        FILTER ( 'Table', 'Table'[ActualValue] = EARLIER ( 'Table'[ActualValue] ) ),
        'Table'[AccType],
        ,
        ASC,
        DENSE
    )
VAR _step1 =
    CALCULATE (
        SUM ( 'Table'[RoundedValue] ) - SUM ( 'Table'[ActualValue] ),
        ALLEXCEPT ( 'Table', 'Table'[AccGroup] )
    )
VAR _step2 =
    IF ( _step1 < 0, INT ( _step1 ) + 1, INT ( _step1 ) )
VAR _step3 =
    IF (
        ABS ( _step2 ) < _rank
            && ABS ( _step2 ) <= 10,
        0,
        IF ( _step2 < 0, 1, -1 )
    )
RETURN
    'Table'[RoundedValue] + _step3

3 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Here is my sample data which is similar to yours.

    You need to create a new column "Step4":

    Step4 =
VAR _rank =
    RANKX (
        FILTER ( 'Table', 'Table'[ActualValue] = EARLIER ( 'Table'[ActualValue] ) ),
        'Table'[AccType],
        ,
        ASC,
        DENSE
    )
VAR _step1 =
    CALCULATE (
        SUM ( 'Table'[RoundedValue] ) - SUM ( 'Table'[ActualValue] ),
        ALLEXCEPT ( 'Table', 'Table'[AccGroup] )
    )
VAR _step2 =
    IF ( _step1 < 0, INT ( _step1 ) + 1, INT ( _step1 ) )
VAR _step3 =
    IF ( ABS ( _step2 ) < _rank, 0, IF ( _step2 < 0, 1, -1 ) )
RETURN
    'Table'[RoundedValue] + _step3

    Here is the result.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-eachen-msft,

      Thanks a lot for the quick response. However, I found few issues with your DAX

      At your example, Look at the CCC AccGroup. The actual Total Value is 43 (rounded(10.7 x 4). Once you rounded AccTypes it rounded to 44, and the variance is 1. This case this variance need to apply CCC6 as -1. This way you could get 43. But for some reason, your DAX is not picking up this. And also what about if you have +/- 10 variance?

      • v-eachen-msft's avatar
        v-eachen-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        According to the results of your picture, I grouped the data in ActualValue.

        So the actual total value of this group is 54(10.7*5). How to explain the "AAA-005" has two diffenrent values if you need to group the data in AccType.

        I am sorry that I ignored the "10" . Here is the new DAX:

        Step4 =
VAR _rank =
    RANKX (
        FILTER ( 'Table', 'Table'[ActualValue] = EARLIER ( 'Table'[ActualValue] ) ),
        'Table'[AccType],
        ,
        ASC,
        DENSE
    )
VAR _step1 =
    CALCULATE (
        SUM ( 'Table'[RoundedValue] ) - SUM ( 'Table'[ActualValue] ),
        ALLEXCEPT ( 'Table', 'Table'[AccGroup] )
    )
VAR _step2 =
    IF ( _step1 < 0, INT ( _step1 ) + 1, INT ( _step1 ) )
VAR _step3 =
    IF (
        ABS ( _step2 ) < _rank
            && ABS ( _step2 ) <= 10,
        0,
        IF ( _step2 < 0, 1, -1 )
    )
RETURN
    'Table'[RoundedValue] + _step3