How to find count of records that are greater than the average value?

[a4apple]

Hi I have 3 columns in my fact table.

CountryKey   DateKey        TimeTaken

      1           20170101         1600

      2           20170101           800

      3           20170101           400

      4           20170101          10000

      1           20170102           1200

My task is to calculate the average of the TimeTaken by Date (ignoring any records that are greater than 5000) and calculate the count of the records that have Timetaken greater than the average Time Taken...

I tried to write the query but I get a Circular dependency error...

Thanks in advance for the help.

[Sean]

@a4apple  Okay you want to ignore the Values over 5000 but you don't specify if you want to count those days or not?

So here are 2 Measures 1 will count the other won't... Hopefully the picture below will clarify what I mean...

MEASURE 1 - this will ignore the days over 5000 in numerator but will count in the denominator

Time by Date MEASURE =
IF (
    HASONEVALUE ( 'Table'[DateKey] ),
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[TimeTaken] ),
            ALLEXCEPT ( 'Table', 'Table'[DateKey] ),
            'Table'[TimeTaken] < 5000
        ),
        CALCULATE (
            COUNTA ( 'Table'[DateKey] ),
            ALLEXCEPT ( 'Table', 'Table'[DateKey] )
        ),
        0
    )
        + 0,
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[TimeTaken] ), 'Table'[TimeTaken] < 5000 ),
        DISTINCTCOUNT ( 'Table'[DateKey] ),
        0
    )
)

MEASURE 2 - will ignore the days over 5000 in both numerator and denominator

Time by Date MEASURE 2 =
IF (
    HASONEVALUE ( 'Table'[DateKey] ),
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[TimeTaken] ),
            ALLEXCEPT ( 'Table', 'Table'[DateKey] ),
            'Table'[TimeTaken] < 5000
        ),
        CALCULATE (
            COUNTA ( 'Table'[DateKey] ),
            ALLEXCEPT ( 'Table', 'Table'[DateKey] ),
            'Table'[TimeTaken] < 5000
        ),
        0
    )
        + 0,
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[TimeTaken] ), 'Table'[TimeTaken] < 5000 ),
        CALCULATE ( DISTINCTCOUNT ( 'Table'[DateKey] ), 'Table'[TimeTaken] < 5000 ),
        0
    )
)

Here are the results with both Measures - take your pick!

Also note how each Measure Calculates in the Total Row!

Hope this helps.

Good Luck!

[a4apple]

@Sean Thanks for the detailed reply.

Yes I don't want to count the records that have Timetaken greater than 5000

so I was expecting more like the average for 20170101 was 933.33

And I am also trying to find the count of records in my fact table that are greater than 933.33 for 20170101 , so my count will be 1 ( since I plan to ignore any thing with time taken more than 5000).

[Sean · Accepted solution]

@a4apple

How about this Measure? I think this is what you are trying to do...

Records Above Average by Date =
IF (
    HASONEVALUE ( 'Table'[DateKey] ),
    CALCULATE (
        COUNTA ( 'Table'[DateKey] ),
        ALLEXCEPT ( 'Table', 'Table'[DateKey] ),
        'Table'[TimeTaken] < 5000,
        FILTER ( 'Table', 'Table'[TimeTaken] > [Time by Date MEASURE 2] )
    )
)

Here's the result...

 

OR...

Good Luck!

[a4apple]

@Sean Thanks for your help. What if I had to do the same in SSAS Tabular. Can I use the same?