Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I would be grateful for any help anyone can give me on this problem, which has been driving me mad over the last 24 hours. I am new to DAX and PBI and normally can work it out with the help of various useful sites, but this one has me stuck.
I have the data below in a single table (this is a selection of columns from that table) and I am trying to summarise the values by category and then show the top category for each team - as shown in the second table.
Team | Category | Value |
A | Apples | 5 |
B | Pears | 7 |
A | Oranges | 4 |
C | Apples | 2 |
A | Apples | 2 |
A | Grapes | 2 |
A | Bananas | 4 |
D | Apples | 6 |
C | Pears | 2 |
C | Pears | 1 |
B | Bananas | 3 |
B | Pears | 4 |
D | Bananas | 5 |
Result I am trying to achieve
Team | Category | Value |
A | Apples | 7 |
B | Pears | 11 |
C | Pears | 3 |
D | Apples | 6 |
I have looked at TopN, Max, SumX but cant work it out and nearly got to the point of just summarising the data in another table, but really hope its possible to produce the result I need without doing that. As I say would really appreciate any help
thanks Ed
Solved! Go to Solution.
i know there are other ways to achieve it but ,can you try this below
1. Add a new table to summarize the Value by team and category
Aggregate = SUMMARIZE(Data,Data[Team],Data[Category],"Value",SUM(Data[Value]))
2. Add 2 below Measures
TotalValue = SUM([Value]) Rank = RANKX(All('Aggregate'[Category]),[TotalValue],,,Dense)
3.Go to Report and drag and Drop the Table and Add Team,Category and Value to it
4. Add the Rank to FIlter and filter to 1. This is the trick we can use filter the top ranked value
we are done. the below is your result 🙂
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |