Calculating Average of a sum of subtotal

[Anonymous]

All,

I am trying to figure out how to calculate an average of a sum of a subtotal (I might be say this wrong - apologies). 

1. Date will be the slicer

2. I want to show a descending list of partners with total units. I have to take the average of the units over multiple days.

for example:

p2 - 420

p3 - 340

p1 - 210

3. Once i show the above in a bar chart, I need an ability to drill into the market level.

Date	Partner	Market	Unit
1/1/2020	p1	New York	10
1/1/2020	p1	San Fran	200
1/1/2020	p2	New York	20
1/1/2020	p2	San Fran	400
1/1/2020	p3	New York	300
1/1/2020	p3	San Fran	40
1/2/2020	p1	New York	10
1/2/2020	p1	San Fran	200
1/2/2020	p2	New York	20
1/2/2020	p2	San Fran	400
1/2/2020	p3	New York	300
1/2/2020	p3	San Fran	40

I checked the forum for a solution. I am not able to figure this out and appreciate any helping hand.

Thanks

Sundar

[v-xuding-msft]

Hi @Anonymous ,

Please try like this:

AVG_Unit =
VAR total_unit =
    CALCULATE ( SUM ( 'Table'[Unit] ), ALLEXCEPT ( 'Table', 'Table'[Partner] ) )
VAR days =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Partner] )
    )
RETURN
    DIVIDE ( total_unit, days )


AVG_Market =
VAR total_unit =
    CALCULATE ( SUM ( 'Table'[Unit] ), ALLEXCEPT ( 'Table', 'Table'[Market] ) )
VAR days =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Market] )
    )
RETURN
    DIVIDE ( total_unit, days )


Measure = IF(ISFILTERED('Table'[Partner]),[AVG_Unit],[AVG_Market])

For more details, please see the attachment.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

I think you need to Avg of Sum. Refer

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

You can also try

averagex(summarize(Table,table[Date],table[Partner],"_sum",table[Unit]),[_sum])

[Anonymous · Accepted solution]

Thanks everyone for your assistance.

I revised the suggestion provided my @amitchandak and this worked for me. Thank You!

AvgUnit =

AVERAGEX (

SUMMARIZE('Table1', 'Table1'[Partner], 'Table1'[Date]),

CALCULATE(sum('Table1'[Unit]))

)

[amitchandak]

@Anonymous , appreciate if you can mark the solution. So others can take advantage.

Appreciate your Kudos.