Count values from measure that's calculated from t...

Hi all

Honestly I feel a bit silly. But I just can't get my head around this supposedly simple problem.

I have a measure (M3) that calculates the ratio between two other measures (M1 and M2). M1 and M2 are based on values from separate tables.

The two separate tables are both related to date, time and location dimensions. Table 1 is further related to a handfull of type dimensions. I need to be able to dynamically slice by both type, date, time and location.

T1 is about 40 mill rows and T2 is about 5 mill rows. Size and not least datasources prevent me from creating a hybrid table so the soulution must be entirely measure based.

M1 averages the sum pr day and hour based on the quarter column.

M2 averages the sum pr day and hour based on the Value column

M3 divides M2 with M1 to return the ratio between the two

Easy.

How do I count the number of times the ratio (M3) exceeds a value (i.e. 5) and display the count in a matrix with location, date, time and types from the different dimensions.

T1 on the left T2 on the right

Table for M1Table for M2