Advance Aggregation / Sum with rounding

[Anonymous]

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. 

[v-eachen-msft]

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]

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 · Accepted solution]

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