cancel
Showing results for
Did you mean:
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 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.

@amitchandak , @Jihwan_Kim @Fowmy , would appreciate your inputs on this.

2 ACCEPTED SOLUTIONS
Community Champion

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] )
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.

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

Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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.

Community Champion

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] )
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.

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

Regular Visitor

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.

Announcements

#### 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.