Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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, i...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 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
Anonymous's avatar
Anonymous
Not applicable
7 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
Icon for Community Support rankCommunity Support
7 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