Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EdEvetts
Helper II
Helper II

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

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. 

 

TeamCategoryValue
AApples5
BPears7
AOranges4
CApples2
AApples2
AGrapes2
ABananas4
DApples6
CPears2
CPears1
BBananas3
BPears4
DBananas5

 

Result I am trying to achieve

 

 

TeamCategoryValue
AApples7
BPears11
CPears3
DApples6

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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 🙂

 

Summarize.PNG

View solution in original post

10 REPLIES 10

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.