Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a4apple
Helper I
Helper I

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

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.

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@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...

 

Avg Time by Date 2.png 

 

OR...

Avg Time by Date 3.png

 

 

Good Luck! Smiley Happy

View solution in original post

4 REPLIES 4
Sean
Community Champion
Community Champion

@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!

 

Avg Time by Date.png

 

 

Also note how each Measure Calculates in the Total Row!

 

Hope this helps.

Good Luck! Smiley Happy

 

@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
Community Champion
Community Champion

@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...

 

Avg Time by Date 2.png 

 

OR...

Avg Time by Date 3.png

 

 

Good Luck! Smiley Happy

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.