Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mk42
Frequent Visitor

First SUM then AVERAGE over the same metric

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.