SUM of AVERAGE

[michaelsparrow]

I have 5 columns:

Ranks,   City,    Date,    Category,          Value

CAP       AKL      12/4    AVAILABLE        24

CAP       AKL      12/4    OFF                    4

CAP       AKL      13/4    AVAILABLE        25

CAP       AKL      13/4    OFF                    4

CAP       WLG     12/4    AVAILABLE        19

CAP       WLG     13/4    OFF                    3

FO        AKL       12/4    AVAILABLE         22
FO        AKL       13/4     OFF                   5
FO        WLG      12/4    AVAILABLE         18
FO        CHC       12/4    AVAILABLE        13

I am trying to get the total number of "Available" Category 'values'. However, to do this I want to have the 'average' of every 'available' value in each city for the period (which is about 4months) and then SUM up the average of each City for each Rank.

How do i do this?

Thanks  

[Phil_Seamark]

Hi @michaelsparrow

Just to help clarify, do you have an example of what you expect the result to be?

[Phil_Seamark]

Might this work??

New Table = SUMMARIZE(
			'Table1',
			[City],
			"Average of Available",CALCULATE(AVERAGE('Table1'[Value]),'Table1'[Category]="AVAILABLE")
			)

[michaelsparrow]

Great this has worked very well. I am nearly there.

1 more question.   Using the method you provided, I have made 2 tables to get 2 Grouped values. The 2 tables are related as attached. I have provided snips of the 2 tables data.

What I am looking to do now is to do a SUM(Average of OFF/Average of Available). The problem is that they aren't linked directly. How do i do this?

Cheers

[v-yulgu-msft · Accepted solution]

Hi @michaelsparrow,

To get the sum of average, you can directly get the desired result in source table via creating calculated columns. Please refer to below DAX formulas.

Average of available =
CALCULATE (
    AVERAGE ( 'SUM of AVERAGE'[Value] ),
    FILTER (
        ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[City] ),
        'SUM of AVERAGE'[Category] = "AVAILABLE"
    )
)

Average of OFF =
CALCULATE (
    AVERAGE ( 'SUM of AVERAGE'[Value] ),
    FILTER (
        ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[City] ),
        'SUM of AVERAGE'[Category] = "OFF"
    )
)

OFF/Available =
'SUM of AVERAGE'[Average of OFF] / 'SUM of AVERAGE'[Average of available]

Sum =
CALCULATE (
    SUM ( 'SUM of AVERAGE'[OFF/Available] ),
    ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[Ranks] )
)

Further more, you can summarize above table to get below output.

New Table =
SUMMARIZE (
    'SUM of AVERAGE',
    'SUM of AVERAGE'[Ranks],
    'SUM of AVERAGE'[City],
    "Sum", MAX ( 'SUM of AVERAGE'[Sum] )
)

Best regards,
Yuliana Gu

[dk_24]

i am also having the similar kind of requirement , can you please have a look on below link.

https://community.powerbi.com/t5/Desktop/Sum-of-Average/m-p/288865#M127101

thanks,

[Phil_Seamark]

[michaelsparrow]

I have 5 columns:

Ranks,   City,    Date,    Category,          Value

CAP       AKL      12/4    AVAILABLE        24

CAP       AKL      12/4    OFF                    4

CAP       AKL      13/4    AVAILABLE        25

CAP       AKL      13/4    OFF                    4

CAP       WLG     12/4    AVAILABLE        19

CAP       WLG     13/4    OFF                    3

FO        AKL       12/4    AVAILABLE         22
FO        AKL       13/4     OFF                   5
FO        WLG      12/4    AVAILABLE         18
FO        CHC       12/4    AVAILABLE        13

I am trying to get the total number of "Available" Category 'values'. However, to do this I want to have the 'average' of every 'available' value in each city for the period (which is about 4months) and then SUM up the average of each City for each Rank.

How do i do this?

Thanks  

[Greg_Deckler]

I believe that your situation is analogous to my Super Groups post:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149