Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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.
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:
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.
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:
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.
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.
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:
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
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:
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,
thanks,
Ram
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
91 | |
86 | |
76 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |