Calculating Average at different levels

[Vvn_789]

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.

[Jihwan_Kim · Accepted solution]

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]

)

https://www.dropbox.com/s/hxaksz156punr2t/vvn.pbix?dl=0 

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Vvn_789]

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 · Accepted solution]

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]

)

https://www.dropbox.com/s/hxaksz156punr2t/vvn.pbix?dl=0 

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

[Vvn_789]

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.