Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

disregard duplicated values in calculation

Hi I have a table with categories with duplicated values and I wanted to have a average, but disregarding the duplicates, does someone know a way to calculate it?

 

The table is like this below, and I have a table like the one shown in the "Value A" but final result shoud be 28,33 as in "Value B"

 

CategoryValue AValue B
A1010
B30 
B3535
C4040
 28,7528,33

 

Thanks guys

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    You could create a calculated column:

    Value B =
IF (
    'Test data'[Value A]
        = CALCULATE (
            LASTNONBLANK ( 'Test data'[Value A], 1 ),
            ALLEXCEPT ( 'Test data', 'Test data'[Category] )
        ),
    'Test data'[Value A],
    BLANK ()
)

     

    Then, insert a table visual, aggreate [Value B] with "Average".

     

    Best regards,

    Yuliana Gu

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    You could create a calculated column:

    Value B =
IF (
    'Test data'[Value A]
        = CALCULATE (
            LASTNONBLANK ( 'Test data'[Value A], 1 ),
            ALLEXCEPT ( 'Test data', 'Test data'[Category] )
        ),
    'Test data'[Value A],
    BLANK ()
)

     

    Then, insert a table visual, aggreate [Value B] with "Average".

     

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      This looks preety good, thanks!