how do I summarize values in a table by category and show all categories with top value?

[EdEvetts]

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

[Sean]

If all you are trying to do is this table - No need for any DAX - just create a Matrix as in the picture...

[EdEvetts]

Thanks for your help Sean, I was looking to take what you have there and create a table that would then just show the top category, so A would be Apples 7, B Pears 11, C Pears 3 and D Apples 6. Would there be any way of doing that ?

thanks Ed

[Sean]

I don't use RANK and TOPN that much so this is not done yet... (the total values are still overall totals)

I gotta go... In the meantime someone may have a better solution...

[EdEvetts]

thanks so much for your help and time Sean its really appreciated, I'll try these out, 

Ed

[Anonymous · Accepted solution]

@EdEvetts

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 🙂

[EdEvetts]

thanks Sarr, really appreciated, i'll try this out 

@Anonymous and it worked a treat

thanks 

Ed

[Sean]

@EdEvetts @Anonymous's solution actually helps mine too

Just add my Rank Category Measure to the Visual Slicers and make => is 1 and here it is

(you can even show who the Top team is for that category if the Team's Best Category was not the best result in that category

such as teams C and D)

[Sean]

@EdEvetts You can actually use my solution in 2 ways (Top Category formula is slightly modified)

1 - To see each Team's Top Category and Value in that Category (and whether they were the best team in that Category)

2 - To see each Category's Top Team and Value (and whether that was the best category for that Team)

(when you have more categories than teams as in this case)

[EdEvetts]

Thanks Sean, i hadnt thought of looking at it from category by team, gives another insight

thanks Ed

[Sean]

@OwenAuger Your answer to the singles/doubles games question - reminded me of the problem in this post.

In my solution - we had to use the Visual Level Filters - do you think we can accomplish this without Visual Level Filters?

Thanks!