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
ispajic
Helper I
Helper I

Count of timestamps with average level greater

Hi, (I think) I'm trying to do something similar to https://community.powerbi.com/t5/Desktop/How-to-find-count-of-records-that-are-greater-than-the-aver... but with a level of aggregation.

 

So, I have a table

TimestampLevelPerson
1,3111
1,3152
1,393
2,291
2,2112
2,283
2,284
3,4211
3,4222
3,483
3,4314
3,4275
3,4896

 

What I'd like to achieve is a COUNT of Timestamps with AVERAGE level within ranges. So, in my example, I'd like to get

CategoryCount
Count of timestamps with average level  <101
Count of timestamps with average level  10-201
Count of timestamps with average level  >201

 

So I can create visuals displaying each category.

 

Source table at https://drive.google.com/open?id=15Uik0PevF4fNtN8hWn5WoTAJTFJvX9I6

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@ispajic

 

Try adding a calculated column as follows

 

Category =
VAR AverageLevel =
    CALCULATE ( AVERAGE ( Table1[Level] ), ALLEXCEPT ( Table1, Table1[Timestamp] ) )
RETURN
    SWITCH (
        TRUE (),
        AverageLevel < 10, "Average Level < 10",
        AverageLevel <= 20, "Average Level 10 -20",
        "Average Level > 20"
    )

 

countTS.png 


Regards
Zubair

Please try my custom visuals

View solution in original post

@ispajic

 

Then you can take a distinctcount of TimeStamps for each category

 

countTS2.png


Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

@ispajic

 

Try adding a calculated column as follows

 

Category =
VAR AverageLevel =
    CALCULATE ( AVERAGE ( Table1[Level] ), ALLEXCEPT ( Table1, Table1[Timestamp] ) )
RETURN
    SWITCH (
        TRUE (),
        AverageLevel < 10, "Average Level < 10",
        AverageLevel <= 20, "Average Level 10 -20",
        "Average Level > 20"
    )

 

countTS.png 


Regards
Zubair

Please try my custom visuals

Works perfectly!

Just a question about aggregating. If I wanted to use the filter in a formula, rather as a visual filter, how would you go about it?

 

I tried:

 

Level1 = CALCULATE(
DISTINCTCOUNT( 'Table'[Category] );
'Table'[Category] = "Average Level < 10")

 

But I alwas get a result of 1, even when I increase the number of timestamps <10

Scratch that, the formula should be 

Level1 = CALCULATE(
DISTINCTCOUNT( 'Table'[Timestamp] );
'Table'[Category] = "Average Level < 10")

@ispajic

 

Then you can take a distinctcount of TimeStamps for each category

 

countTS2.png


Regards
Zubair

Please try my custom visuals

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.