How do I add the same hierarchy twice to a metric set? I’ve run into things before where I wanted to, say, compare a count and distinct count of a hierarchy. Now I would like to add a count of strength names for sorting when the strength names are already on the horizontal axis, so if I add the hierarchy again, it takes it off the axis. Is there an easy way to re-use the hierarchy in multiple places, aggregated differently?
How to add count of hierarchy measure to metric set already using that hierarchy
Hey, you can add the same hierarchy twice if it is present in the source data twice. So you can add a calculated element and return the same field in the cube. Alternatively, you can add a formula measure returning some specific values based on hierarchy field values and then use the formula measure (can be hidden) field the same way the hierarchy field is used.
If this does not fit the requirements, have you thought of using two metric sets in the same visualization? They can use the same hierarchy under ROWS but different measures with different aggregators - like I used AVG and SUM for total_rooms.
1 Like
I’m not sure how to add a formula measure into the same metric set with the hierarchy. It might be easier to talk about the specific example. So below, I want to be able to sort the strength domains (the 4 colors) first in order of number of Associates, and then, if that number is the same, by total number of strengths (determined by a count of strength names). However, StrengthName is already on rows, so if I add it to Measures, it takes it off of rows.
Your example I think would be helpful for when I’m digging into data and want to aggregate different ways, though! I didn’t realize you could add multiple metric sets to the same visualization.
Hi Lindsey,
Not sure if this is helpful because I don’t fully comprehend, my apologies. It looks to me like your strength domains are sorted by number of associates which you wanted but wouldn’t each strength name return a count of 1 since each strength name shows up once? Are you wanting to count how many strengths are in each strength domain? If so, you could use a count measure and then a formula for SUM($Count_Measure, $Strenght_Domain) Also as you mentioned in the other post a workaround would be to add a second strength name column at the cube level but that can be a pain.
Hope this helps.
1 Like
Currently, this is sorting by number of associates grouped first by domain, so it sorts the domains by the total number of Associates and then also the strengths within each domain. I was hoping something similar might be possible for the strength name, but I hadn’t thought of using a count measure! Works great, thank you!
You use is a valid one. Therefore, the next version will allow user to add the same hierarchy multiple times in a metric set, provided the hierarchy is used as a measure with with various aggregators (i.e. count or distinct count).
2 Likes