Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |