cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

My report design,

level – Country -> City

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

regards,

Ram

2 ACCEPTED SOLUTIONS
Community Support

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.

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:

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.

Community Support

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:

And you can get what you want.

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.

8 REPLIES 8
Community Support

Community Support

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.

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:

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.

Helper III

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

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

thanks,

Ram

Community Support

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!

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.

Helper III

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

please check and let me know if any issue

Community Support

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:

And you can get what you want.

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.

Helper III

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,

thanks,

Ram

Community Support

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?

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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors