cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vvn_789
Regular Visitor

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 AmountDeptStoresAreaRegion
$2FurnitureStore 1Area 1Region 1
$3ClothingStore 1Area 1Region 1
$4FurnitureStore 2Area 1Region 1
$5ClothingStore 2Area 1Region 1
$7FurnitureStore 3Area 1Region 1
$8ClothingStore 3Area 1Region 1
$9FurnitureStore 7Area 1Region 1
$16ClothingStore 8Area 2Region 1
$11FurnitureStore 9Area 2Region 1
$15ClothingStore 10Area 2Region 1
$19FurnitureStore 11Area 2Region 1
$22ClothingStore 12Area 2Region 1
$28FurnitureStore 13Area 2Region 1
$7ClothingStore 14Area 2Region 1
$7FurnitureStore 15Area 3Region 2
$9ClothingStore 16Area 3Region 2
$9FurnitureStore 17Area 3Region 2
$7ClothingStore 18Area 3Region 2
$8FurnitureStore 19Area 3Region 2
$9ClothingStore 20Area 3Region 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.

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Community Champion
Community Champion

Hi, @Vvn_789 

Please check the below picture and the sample pbix file's link down below.

 

Picture1.png

 

Sales Total =
SUM ( 'Table'[Sales Amount] )
 
Area Avg =
VAR selectedarea =
MAX ( 'Table'[Area] )
VAR selecteddept =
MAX ( 'Table'[Dept] )
RETURN
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[Area] = selectedarea
&& 'Table'[Dept] = selecteddept
),
'Table'[Sales Amount]
)
 
Region Avg =
VAR selectedregion =
MAX ( 'Table'[Region] )
VAR selecteddept =
MAX ( 'Table'[Dept] )
RETURN
AVERAGEX (
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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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. 

Jihwan_Kim
Community Champion
Community Champion

Hi, @Vvn_789 

Please check the below picture and the sample pbix file's link down below.

 

Picture1.png

 

Sales Total =
SUM ( 'Table'[Sales Amount] )
 
Area Avg =
VAR selectedarea =
MAX ( 'Table'[Area] )
VAR selecteddept =
MAX ( 'Table'[Dept] )
RETURN
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[Area] = selectedarea
&& 'Table'[Dept] = selecteddept
),
'Table'[Sales Amount]
)
 
Region Avg =
VAR selectedregion =
MAX ( 'Table'[Region] )
VAR selecteddept =
MAX ( 'Table'[Dept] )
RETURN
AVERAGEX (
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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors