Forum Discussion
Calculating Average at different levels
Hi All,
I am trying to calculate the Area average and Region average based on store selection, below is a sample dataset that might be useful to understand what I am trying to achieve.
| Sales Amount | Dept | Stores | Area | Region |
| $2 | Furniture | Store 1 | Area 1 | Region 1 |
| $3 | Clothing | Store 1 | Area 1 | Region 1 |
| $4 | Furniture | Store 2 | Area 1 | Region 1 |
| $5 | Clothing | Store 2 | Area 1 | Region 1 |
| $7 | Furniture | Store 3 | Area 1 | Region 1 |
| $8 | Clothing | Store 3 | Area 1 | Region 1 |
| $9 | Furniture | Store 7 | Area 1 | Region 1 |
| $16 | Clothing | Store 8 | Area 2 | Region 1 |
| $11 | Furniture | Store 9 | Area 2 | Region 1 |
| $15 | Clothing | Store 10 | Area 2 | Region 1 |
| $19 | Furniture | Store 11 | Area 2 | Region 1 |
| $22 | Clothing | Store 12 | Area 2 | Region 1 |
| $28 | Furniture | Store 13 | Area 2 | Region 1 |
| $7 | Clothing | Store 14 | Area 2 | Region 1 |
| $7 | Furniture | Store 15 | Area 3 | Region 2 |
| $9 | Clothing | Store 16 | Area 3 | Region 2 |
| $9 | Furniture | Store 17 | Area 3 | Region 2 |
| $7 | Clothing | Store 18 | Area 3 | Region 2 |
| $8 | Furniture | Store 19 | Area 3 | Region 2 |
| $9 | Clothing | Store 20 | Area 3 | Region 2 |
I am trying to get a clustered bar graph that has all the departments on the axis. For the values, I have a sum of the Sales Amount. I have to get the Area average and Region average for the department in the values and these values should be based on Store Slicer.
For Eg. If I select Store 1 from the slicer, the first bar in the clustered bar graph, the department furniture should display value as $2 for the store1, Area average- the second bar should give me a value of $5.5 (2+4+7+9)/4, values for dept-furniture for Area 1/ count of stores) and the third bar is the Region average which should be $11.42 (2+4+7+9+11+19+28)/7, values for dept-furniture for Region 1
Need your inputs to achieve the Area average and Region average.
Thank you for your time!!
amitchandak , Jihwan_Kim Fowmy , would appreciate your inputs on this.
Hi, Vvn_789
Please check the below picture and the sample pbix file's link down below.
Sales Total =SUM ( 'Table'[Sales Amount] )Area Avg =VAR selectedarea =MAX ( 'Table'[Area] )VAR selecteddept =MAX ( 'Table'[Dept] )RETURNAVERAGEX (FILTER (ALL ( 'Table' ),'Table'[Area] = selectedarea&& 'Table'[Dept] = selecteddept),'Table'[Sales Amount])Region Avg =VAR selectedregion =MAX ( 'Table'[Region] )VAR selecteddept =MAX ( 'Table'[Dept] )RETURNAVERAGEX (FILTER (ALL ( 'Table' ),'Table'[Region] = selectedregion&& 'Table'[Dept] = selecteddept),'Table'[Sales Amount])Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
4 Replies
Hi, Vvn_789
Please check the below picture and the sample pbix file's link down below.
Sales Total =SUM ( 'Table'[Sales Amount] )Area Avg =VAR selectedarea =MAX ( 'Table'[Area] )VAR selecteddept =MAX ( 'Table'[Dept] )RETURNAVERAGEX (FILTER (ALL ( 'Table' ),'Table'[Area] = selectedarea&& 'Table'[Dept] = selecteddept),'Table'[Sales Amount])Region Avg =VAR selectedregion =MAX ( 'Table'[Region] )VAR selecteddept =MAX ( 'Table'[Dept] )RETURNAVERAGEX (FILTER (ALL ( 'Table' ),'Table'[Region] = selectedregion&& 'Table'[Dept] = selecteddept),'Table'[Sales Amount])Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Thank you Jihwan_Kim for your response. I was able to achieve the expected output. One quick question regarding handling this through RLS, if the user has access to the current store but should be able to see area and region average. When I log in as a user the average for both region and area shows incorrect values, I believe it's because it only considers the different store user has access to and averages based on it.
Thank you Ashish_Mathur for your response. I was able to achieve the expected output. One quick question regarding handling this through RLS, if the user has access to the current store but should be able to see area and region average. When I log in as a user the average for both region and area shows incorrect values, I believe it's because it only considers the different store the user has access to and averages based on it.
