Sum the average of categories

[muggydaniel]

Hi all

I need to be able to sum the average of different tasks per state where each individual row represents 1 instance

Data: 

State - Animal - Amount Trigger

NSW - Cows - 62 

NSW - Cows - 62

NSW - Chickens - 10 

NSW - Chickens - 10

NSW - Horses - 52 

NSW - Horse - 52

NSW - Horse - 52

Where a matrix looks like the below

NSW (total is the sum of the amount triggers = 124 not the average across all individual lines)

- Cow - 62 

- Chicken - 10 

- Horse - 52

Help please!

[muggydaniel]

Thanks heaps it works!

[sevenhills · Accepted solution]

Sum - Average of Amount Trigger per Animal = 
 
    If ( HASONEVALUE('Table Category'[Animal]),
        AVERAGE('Table Category'[Amount Trigger]), 
        CALCULATE( SUMX( SUMMARIZE( 'Table Category', 'Table Category'[Animal]), CALCULATE( AVERAGE('Table Category'[Amount Trigger]))))
    )

Adjust the format as number if needed.