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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

@Anonymous 

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
AlB
Super User
Super User

Hi @Anonymous 

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]
)
Anonymous
Not applicable

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)

@Anonymous 

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]
)

 

Anonymous
Not applicable

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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