Sum of average sales at the postal code level

[Mainer04401]

I have sales data for my stores and in another column on the same worksheet I have the aggregate sales of all stores in that same postal code (the trading area).

Trouble is, when I have multiple stores in the same zip code, the trading area's sales are duplicated. For example, if I have 3 stores in the same zip code, the trading area sales are listed 3x.

I thought I had a working formula below:

sumx(values(Table[Postal Code]),
calculate(average(Table[Trading Area Sales),
allexcept(Table,Table[Postal Code],Table[Product],Table[Week])))

This calculates a sum of the average sales in each postal code.

I chose average because I thought it would solve the issue of trading area sales being repeated. Unfortunately, this only works when I have one time period and one product in the view or filtered. Otherwise, in my example of 3 stores in the same postal code, instead of dividing the sum by 3, it would divide the sum by 6 if I have 2 products selected. Any suggestions on a better formula?

Here is a table to illustrate what I'm trying to accomplish ...  The postal code sales column takes an average of all sales at the postal code level and then sums the averages in the total. 

Store Name	Postal Code	Sales	Postal Code Sales
Store A	06385	100	1,000
Store B	06385	200	1,000
Store C	90210	300	3,000
Store D	90210	300	3,000
TOTAL		900	4,000

[Mainer04401]

The formula below APPEARS to do what I need but if there is a cleaner, more efficient way, I would love to hear it!

sumx(values(Table[Postal Code]),

sumx(values(Table[Product]),

sumx(values(Table[Week],
calculate(average(Table[Trading Area Sales),
allexcept(Table,Table[Postal Code],Table[Product],Table[Week])))

Essentially I need PowerBI to sum the average of each product's sales, during each week, at the postal code level in order to remove duplicates at the postal code level.

[mwegener · Accepted solution]

Hi @Mainer04401 ,

try this

SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Postal Code],
        'Table'[Product],
        'Table'[Week],
        "@AVG", CALCULATE (
            AVERAGE ( 'Table'[Trading Area Sales] ),
            ALLEXCEPT ( 'Table', 'Table'[Postal Code], 'Table'[Product], 'Table'[Week] )
        )
    ),
    [@AVG]
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Mainer04401]

@mwegener- Thank you!  That worked and that formula loads much quicker than the formula I used so it must be much more efficient!  

[amitchandak]

Refer to this

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

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin