cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Christien Frequent Visitor
Frequent Visitor

Aggregate: Get value that occurs most often

Hi all,

I'm fairly new to Power BI and I am now trying to aggregate a table where there is a text column with different entries within a group and I want to get the entry that occurs most often (i'll give an example below). I've been looking on the internet but can't find the proper way to do it but I can't find the thing that I'm looking for (tho I'm sure more people desire this so I'm probably not using the right keywords...).

 

Anyway, an example data set (simplified as I have more columns that need (simple) aggregation):

 

ClientIDLocationIDRevenueType
1110Type1
2120Type1
3210Type2
4315Type2
5315Type2
6330Type1

 

Now I aggregate by locationID en sum the Revenue and I want to Type that occurs most often within the group. resulting in the following

 

LocationID Revenue Type

130Type1
210Type2
360Type2

 

How would I go about this? I first considerd grouping by location ánd type and then count the number of rows as to know which type occurs most often. But then I don't know how to aggregate by location and being left with the type that occcurs most often while still summing over all the revenue.

 

I hope someone is able to help me! Thanks in advance

 

EDIT:

It might be useful to know that I aim to make a bar chart (or any type of chart) based on the new table to diplay, for example, the average revenue per location. The Type is then used as a slicer so I can eliminate certain type of locations.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Aggregate: Get value that occurs most often

@Christien 

It sounds like you'd be able to do that with what you have at the beginning already. Where does the most frequent type come into the picture? I'm not sure I fully understand, but if you want the contents that we produced before in the visual in an actual table, you can create a calculated table as below, where [Measure] is the measure we created above and Table1 is the original table: 

NewTablee =ADDCOLUMNSS (
    DISTINCT (Table11[LocationID] );
    "Revenue"; CALCULATE ( SUM (Table11[Revenue] ) );
    MostFrequentTypee"; [Measure]
)

 

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Aggregate: Get value that occurs most often

Hi @Christien 

Try this:

1. Place LocationID in a table visual

2. Place Revenue in a table visual and select it to be shown as Sum (or, rather,create an explicit measure doing this)

3. Create this easure and place it in the visual as well

Measure =
MINX (
    TOPN (
        1;
        DISTINCT ( Table1[Type] );
        CALCULATE ( COUNT ( Table1[Type] ) ); DESC
    );
    [Type]
)
Christien Frequent Visitor
Frequent Visitor

Re: Aggregate: Get value that occurs most often

Thank you @AlB ! This indeed does what I was aiming for.

Follow up question an this is whether I will be able to make a visual based on this table. 

My plan is to display a bart chart of the average revenue per location and I want to add a slicer for the type.

 

(The main post has been edited to include that I want to make a bar chart out of it as well)

Super User
Super User

Re: Aggregate: Get value that occurs most often

@Christien 

It sounds like you'd be able to do that with what you have at the beginning already. Where does the most frequent type come into the picture? I'm not sure I fully understand, but if you want the contents that we produced before in the visual in an actual table, you can create a calculated table as below, where [Measure] is the measure we created above and Table1 is the original table: 

NewTablee =ADDCOLUMNSS (
    DISTINCT (Table11[LocationID] );
    "Revenue"; CALCULATE ( SUM (Table11[Revenue] ) );
    MostFrequentTypee"; [Measure]
)

 

View solution in original post

Christien Frequent Visitor
Frequent Visitor

Re: Aggregate: Get value that occurs most often

Thank you @AlB for your quick responses.

I don't think I can do what I want with just the original table.

 

There are two reasons I think this, but it could be just me not knowing the full capabilities of powerBI.

1) I don't know how to sum the revenues per LocationID and then take the average of all these values (all in one visual). There is also another grouping column (month) in place which I didn't mention above, but this value goes on the x-axis to create a timeline/comparison. Since I didn't think it was possible to do such grouping by and then taking the average within a visual I opted for first created a query with the aggregated table.

2) Even if the above was possible, I would still need to figure out which type belongs to which LocationID. If I were to simple use the slicer to eliminate one Type I would eliminate the rows from the original data, hence deleting the revenue of the sixth row (in the original example) ending up with a revenue of 30 for location 3, while it should be a revenue of 60.

 

To give a bit more context. One location can have different values of [Type] due to mistakes in registration ([Type] is registered at client level and not at location level), hence I want to find a way to assign one value of [Type] to a location such that I can eliminate the entire location from a graph and not just those entries where on the client level this value of [Type] was assigned.

For example, location 3 is a House. For two clients it is registered as [Type] = House, for the third client it is registered as [Type] = Company.    If I want to see the average revenue per [Type] House I want to keep all revenue of location 3 in the set, not just those where someone mistakenly selected [Type] = Company.

 

Nonetheless, I've just tried the method for the calculated table and that seemed to do the trick.

I just wonder now what the difference is between a query and a calculated table. I did aggregations as queries, but it seems like the same result can be accomplished by using calculated tables. Is there a reason to do the one over the other (apart from in this case the fact that it allows me to get the most frequent item)?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 149 members 1,927 guests
Please welcome our newest community members: