Hello!
I am hoping to find a simple solution to the following issue:
I have a large set of ~50 products, which varies from month to month. I am visualizing them in a pie chart (I know, I know), by sum of kWh energy savings. I am using a quick table calculation to represent the slices as percent of total kWh energy savings.
It looks like this:
I would like to group all categories that are under 2% of total kWh savings into an "other" field. Here's the catch - I want to do it dynamically, so that I can change my filters (ie the month, the region, etc), and have my "other" field automatically recalculate...I also want it to be dynamic for when new data comes in, when categories are added to or fall off my radar, or when I want to look at other specific criteria.
It seems like this should be simple, but I'm stumped. I could do this easily if I wanted to create an "other" field based on a fixed number, or a hierarchy set, etc. It's the fact that I want to do it dynamically by percent of total that seems to stump me (or tableau).
My initial solution was to rename the product name as "other" based on a calculation, ie if the percentage of sum of total was less than 2% - it failed because I was mixing aggregated and non-aggregated fields.
My failed attempt:
if SUM([Total Reported Gross kWh]) / TOTAL(SUM([Total Reported Gross kWh]))>.02 then [Measure Name] else 'Other' end
Any suggestions? I cannot share the original data book, but I could possibly make an analogous mock up if necessary.
Thanks!
