First SUM then AVERAGE over the same metric

[mk42]

Hi,

It's somewhat PowerBI and some data modeling questions.

I have a metric in my DB: sales per store per region. What is the best way to display the metric over multiple weeks nationally?

The problem is, my calculations for this metric would be:

1. national sales per store per product = sum all sales / sum # of stores across regions

2. average of the number from step 1 per week over 4 (or another number) weeks

I solved that before by having a DAX aggregate table where I had SUMs and then I displayed a measure or column from there as an AVERAGE. But maybe there is a more elegant way to do that.

[amitchandak · Accepted solution]

One way is check this

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

You might exchange sum and Avg

Another one is

formula 1
Avg Amount = SUM('Table'[Amount])

formula 2
Sum Amount = 
SUMX(
    VALUES(Table[ID])  // Avg till this level
    , [Avg Amount]
)

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks