topic Re: Calculate average at different hierarchy levels in Matrix in DAX Commands and Tips

Calculate average at different hierarchy levels in Matrix

AJW2023 — Fri, 13 Jan 2023 02:04:36 GMT

I am attempting to calculate the average itmes sold across different rows (hierarchy) of a matrix table to then compare to the actual number sold for each month and identify through conditional formating months where the number of items sold were above the average.

Example:

Calulate the average sold per month for 'Site A' (44/12 = 3.67) to then be able to identify the months where 'Site A' sales was above the average.

Then calculate the average sold per month for area 'Blue' (next level of the hierarchy) and identify the months where 'Blue' area items sold were above the average.

Thanks

Re: Calculate average at different hierarchy levels in Matrix

amitchandak — Fri, 13 Jan 2023 02:33:41 GMT

AJW2023 , Assuming you only want to change grand total

First, create a measure

M1= Sum(Table[Number sold])

Then create and use this measure

m2= Averagex(Values(Table[Month]), [M1])

Re: Calculate average at different hierarchy levels in Matrix

AJW2023 — Fri, 13 Jan 2023 03:27:29 GMT

Thats amitchandak . That has calculated the averages for each of the rows as I was hoping.

Any ideas how to then compare each months total per cagegory and display if it is greater than the average using conditional formating?

For example for "Site A" the average is 3.67 therefore the months where the number sold were greater than the average = January, Feburary, May, July, August and September and highlight these values Red