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.
I have the below table. The task is to count number of occurrences of each category per each month and then present top ten (count wise) categories on the 100% stacked column chart. I am able to count occurrences , but then power bi sums them for selected period of time (multiple months for example). I tried to rank them, but if number of occurrences is same, then the same rank is assigned and this leads to incorrect data as numbers are added again. Your help is greatly appreaciated. Using powerBi desktop with report server.
Item | Created Month | Category | Assigne |
Item A | Jan-20 | X | F |
Item B | Feb-20 | Y | E |
Item C | Jan-20 | Z | D |
Item A | Apr-20 | X | D |
Item B | Jan-20 | Y | D |
Item C | Feb-20 | Z | E |
Item A | Mar-20 | Z | F |
Item B | Jan-20 | Z | F |
Item C | Mar-20 | Y | F |
Solved! Go to Solution.
Hi @seba
can you give this a go:
number of occurences =
CALCULATE (
COUNT ( 'Table'[Item] ),
FILTER (
VALUES ( 'Table'[Category] ),
RANKX (
ALL ( 'Table'[Category] ),
CALCULATE ( COUNT ( 'Table'[Item] ) )
+ INT ( CALCULATE ( UNICODE ( MIN ( 'Table'[Category] ) ) ) ) / 100000,
,
DESC
) <= 10
)
)
The
+ INT ( CALCULATE ( UNICODE ( MIN ( 'Table'[Category] ) ) ) ) / 100000
-part is for breaking any ties
Cheers,
Sturla
Hi @seba
can you give this a go:
number of occurences =
CALCULATE (
COUNT ( 'Table'[Item] ),
FILTER (
VALUES ( 'Table'[Category] ),
RANKX (
ALL ( 'Table'[Category] ),
CALCULATE ( COUNT ( 'Table'[Item] ) )
+ INT ( CALCULATE ( UNICODE ( MIN ( 'Table'[Category] ) ) ) ) / 100000,
,
DESC
) <= 10
)
)
The
+ INT ( CALCULATE ( UNICODE ( MIN ( 'Table'[Category] ) ) ) ) / 100000
-part is for breaking any ties
Cheers,
Sturla
Wow, that is amazing and impressive! It does work! Thank you very much!
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 |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
140 | |
106 | |
100 | |
83 | |
73 |