Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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" )
Then you can take a distinctcount of TimeStamps for each category
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" )
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")
Then you can take a distinctcount of TimeStamps for each category
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |