Forum Discussion
Aggregate: Get value that occurs most often
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] )
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]
)
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)?