Aggregate: Get value that occurs most often

[Anonymous]

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

ClientID	LocationID	Revenue	Type
1	1	10	Type1
2	1	20	Type1
3	2	10	Type2
4	3	15	Type2
5	3	15	Type2
6	3	30	Type1

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

1	30	Type1
2	10	Type2
3	60	Type2

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.

[AlB]

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]

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)

[AlB · 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]
)

[Anonymous]

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