Forum Discussion
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):
| 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.
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] )
4 Replies
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] )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] )