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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AndreiK15
Helper II
Helper II

Average won't return correct value

 Hi,

 

I'm trying to get average, but the return amount is wrong. I am using the followig formulas:

 

Sales Margin Format = CALCULATE(SUM(Sales[Sales Margin Amt]),ALLEXCEPT(Sites,Sites[Site Type 02]))
 
and I get 173.61M which is correct, but when I use the following formula:
 
Sales Margin Format (avg) = CALCULATE(AVERAGE(Sales[Sales Margin Amt]),ALLEXCEPT(Sites,Sites[Site Type 02]))
 
I get 1.15 and the average should be between 110k - 140k.
 
Thanks!
 
 
1 ACCEPTED SOLUTION
AndreiK15
Helper II
Helper II

Hi @amitchandak

 

I need average for site level 02. Site level 02 contain (STANDARD 1, STANDARD 2, CITY 1, CITY 2 etc.). I have found the solution though, but maybe you can tell if there is something else without combing all these. What I did is:

 

Count Format = CALCULATE(COUNTA(Sites[Site Type 02]),ALLEXCEPT(sites,Sites[Site Type 02]))

to get count on all stores for each site type 02

then

Sales Margin Format (avg) = DIVIDE([Sales Margin Format],[Count Format])
 
and I get the desired result. I can combine all these using var. Don't know though why when I use average as in my first post don't get the same value.
 
Thanks!

View solution in original post

2 REPLIES 2
AndreiK15
Helper II
Helper II

Hi @amitchandak

 

I need average for site level 02. Site level 02 contain (STANDARD 1, STANDARD 2, CITY 1, CITY 2 etc.). I have found the solution though, but maybe you can tell if there is something else without combing all these. What I did is:

 

Count Format = CALCULATE(COUNTA(Sites[Site Type 02]),ALLEXCEPT(sites,Sites[Site Type 02]))

to get count on all stores for each site type 02

then

Sales Margin Format (avg) = DIVIDE([Sales Margin Format],[Count Format])
 
and I get the desired result. I can combine all these using var. Don't know though why when I use average as in my first post don't get the same value.
 
Thanks!
amitchandak
Super User
Super User

@AndreiK15 , This is line level average. Are looking for site level Avg ?

 

Sales Margin Format (avg) = CALCULATE(AVERAGEX(values(Sites[Site]) , Calculate(Sum(Sales[Sales Margin Amt]))),ALLEXCEPT(Sites,Sites[Site Type 02]))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.