Summing and Averaging By Matrix Level

[Anonymous]

Hello. Working in different levels of a matrix. The levels are Date->Hour->City->Locations. City names are blocked out in red.

I am trying to sum all the values of the locations level so that the city level shows that sum (1,092 & 377 in example below). Then sum at the hour level by city (1469,1400,1491) and then average at the date level. (1469+1400+1491/3=1,453). Basically everything in the photo is correct except that I want to average the date level by hour instead of summing all the hours together. Thanks!

Current code for the No Checks column:

Avg NC =

SWITCH (

    TRUE (),

    NOT ISFILTERED ( 'Table'[DATA_HR] ), AVERAGEX ( VALUES ( 'Table'[DATA_HR] ), CALCULATE ( SUM ( 'Table'[NC_COUNT] ) ) ),

    SUM( ( 'Table'[NC_COUNT] )

))

[Daniel29195 · Accepted solution]

@Anonymous 

Hello, 

use isinscope ()  function..

and change the calculation depending on which level you are in the matrix. 

NB :  when using isinscope start from the lowest level , check below .

measure = 

switch( 

true() , 

isinscope(location ) ,  -- your calcultion , 

isinscope(cityt) ,  -- your calcultion , 

isinscope(hour) ,  -- average 

isinscope(date) ,  -- your calcultion , 

) 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍