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
Ramachandran
Helper III
Helper III

How to calculate average based on levels (Country -> City)

Hi, 

I’m facing the issue is below, while average is working in city level but not working as country level.

My data is look like

Ramachandran_0-1615575222215.png

 

My report design,

level – Country -> City

Ramachandran_1-1615575222221.png

 

Here Average is not working as expected in country level for example,

IND – 1.19 and US 0.62, actually, Overall average I’m trying to calculated sum of sales/no of country.

I would expect Overall average is (1.19 + 0.62) / 2 = 0.90 but it’s returned 0.85.

I don’t know, what is the issue. Someone suggests me.

 

Fyi,  it is working fine in city level

Ramachandran_2-1615575222225.png

 

regards,

Ram

2 ACCEPTED SOLUTIONS
v-robertq-msft
Community Support
Community Support

Hi, @Ramachandran 

According to your description and sample picture, I don’t think the Overall average is simply calculated as you expected as (1.19 + 0.62) / 2 = 0.90 because “US” has three cities and “Ind” has two cities, so the US occupies a greater weight in the calculation of the average, it’s like (0.16+0.73+0.96+0.46+1.90)/5=0.85.

v-robertq-msft_0-1615863281985.png

 

Therefore, I think the average calculation you used is correct and there’s no need for you to make any change.

If you still want to get the calculation like “0.90”, you can try this measure:

Average =

var _US=

IF("US" in SELECTCOLUMNS('Table',"1",[Country]),AVERAGEX(FILTER(ALL('Table'),[Country]="US"),[sal%]),0)

var _UK=

IF("UK" in SELECTCOLUMNS('Table',"1",[Country]),AVERAGEX(FILTER(ALL('Table'),[Country]="UK"),[sal%]),0)

var _IND=

IF("IND" in SELECTCOLUMNS('Table',"1",[Country]),AVERAGEX(FILTER(ALL('Table'),[Country]="IND"),[sal%]),0)

var _sum=

_US+_UK+_IND

return

DIVIDE(_sum,DISTINCTCOUNT('Table'[Country]))

 

And you can get what you want, like this:

v-robertq-msft_1-1615863281992.png

 

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

Hi, @Ramachandran 

According to your sample pbix file, I opened it and added the measure I posted above then changed the measure [Country Sal Avg] like this:

Country Sal Avg1 =
VAR __SelectedLabel = SELECTEDVALUE('Country_Summary'[Country])// 'Table 2'[Month])
 
RETURN
SWITCH(__SelectedLabel,
"_Average of Country", [Average],
CALCULATE(AVERAGE('Country'[sal %]), FILTER('Country', 'Country'[Country] = __SelectedLabel))
)

Then the average value in the column chart can display the value you wanted, like this:

v-robertq-msft_0-1615961512531.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

8 REPLIES 8
v-robertq-msft
Community Support
Community Support

 
v-robertq-msft
Community Support
Community Support

Hi, @Ramachandran 

According to your description and sample picture, I don’t think the Overall average is simply calculated as you expected as (1.19 + 0.62) / 2 = 0.90 because “US” has three cities and “Ind” has two cities, so the US occupies a greater weight in the calculation of the average, it’s like (0.16+0.73+0.96+0.46+1.90)/5=0.85.

v-robertq-msft_0-1615863281985.png

 

Therefore, I think the average calculation you used is correct and there’s no need for you to make any change.

If you still want to get the calculation like “0.90”, you can try this measure:

Average =

var _US=

IF("US" in SELECTCOLUMNS('Table',"1",[Country]),AVERAGEX(FILTER(ALL('Table'),[Country]="US"),[sal%]),0)

var _UK=

IF("UK" in SELECTCOLUMNS('Table',"1",[Country]),AVERAGEX(FILTER(ALL('Table'),[Country]="UK"),[sal%]),0)

var _IND=

IF("IND" in SELECTCOLUMNS('Table',"1",[Country]),AVERAGEX(FILTER(ALL('Table'),[Country]="IND"),[sal%]),0)

var _sum=

_US+_UK+_IND

return

DIVIDE(_sum,DISTINCTCOUNT('Table'[Country]))

 

And you can get what you want, like this:

v-robertq-msft_1-1615863281992.png

 

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

Thank you for your quick response..

I have downloaded and verified the .pbix file, the average is showing is correct. but, the overall country average column is not shown in here,

I would the output is link, Average of Country level -> Average of City level

Ramachandran_0-1615882235060.png

if you can possible, please add the column or give suggestion for this.

 

thanks,

Ram

 

Hi, @Ramachandran 

According to your picture, I’m wondering how can you add an “Average of country” column in the left of your column chart. The measure I created can only get one average value based on the country slicer. Would you like to post your pbix file(without sensitive data)?

Thanks very much!

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

I couldn't add .pbix file here. so, I attached file in google drive and link is below,

https://drive.google.com/file/d/1kCUCwe_w_uDrvi1IRFu8xq5lQpgGTWJz/view?usp=sharing

 

please check and let me know if any issue

 

Hi, @Ramachandran 

According to your sample pbix file, I opened it and added the measure I posted above then changed the measure [Country Sal Avg] like this:

Country Sal Avg1 =
VAR __SelectedLabel = SELECTEDVALUE('Country_Summary'[Country])// 'Table 2'[Month])
 
RETURN
SWITCH(__SelectedLabel,
"_Average of Country", [Average],
CALCULATE(AVERAGE('Country'[sal %]), FILTER('Country', 'Country'[Country] = __SelectedLabel))
)

Then the average value in the column chart can display the value you wanted, like this:

v-robertq-msft_0-1615961512531.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

Sorry for the multiple request,

 

I can get "Average Country" based on Country slicer and its working fine. Now, I want to add one more slicer for "City" then what I do? Can you give suggestion?

The report like,

Ramachandran_0-1616160089552.png

 

thanks,

Ram

 

 

v-robertq-msft
Community Support
Community Support

Hi, @Ramachandran 

Would you like to post your sample data in the “Form” type so that we can just copy and paste it into our pbix file to help you in advance?

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

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.