Count of timestamps with average level greater

[ispajic]

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

Timestamp	Level	Person
1,3	11	1
1,3	15	2
1,3	9	3
2,2	9	1
2,2	11	2
2,2	8	3
2,2	8	4
3,4	21	1
3,4	22	2
3,4	8	3
3,4	31	4
3,4	27	5
3,4	89	6

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

Category	Count
Count of timestamps with average level  <10	1
Count of timestamps with average level  10-20	1
Count of timestamps with average level  >20	1

So I can create visuals displaying each category.

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

[Zubair_Muhammad · Accepted solution]

@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"
    )

 

[Zubair_Muhammad · Accepted solution]

@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"
    )

 

[ispajic]

Works perfectly!

[ispajic]

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

[ispajic]

Scratch that, the formula should be 

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

[Zubair_Muhammad · Accepted solution]

@ispajic

Then you can take a distinctcount of TimeStamps for each category