Hi all, does anyone know if there’s a way to remove the dimension hierarchy from pivot tables? I need to calculate a distinct count by several different dimensions in various different combinations, e.g. if I have dimensions A, B and C I need to be able to see distinct counts by: ABC, AB, BC, AC, A, B and C. However when I set up a pivot table, I can only get values for some of these: ABC, AB and A. Does anyone know how to get out all the values I want, without having to make multiple pivot tables?
Hello,
You have to build each combination that you need as a calculated field.
For example, if my values (A,B,C,…) are, "Customer, “Country”, “Product” and “OrderID” is my dimension I would do:
Combination1: distinctCountOver({Order ID},[Customer,Country,Product],PRE_FILTER)
Combination2: distinctCountOver({Order ID},[Customer,Country],PRE_FILTER)
and so on…
Hi Jose, this does seem useful, but my client has 5 different dimensions they want, which would result in 31 calculated fields. I’ve been playing around with different combinations, but I’m struggling to make a pivot table that I think all my users can easily understand. If you have the ability to make feature suggestions, would you be able to add “collapse individual dimensions in pivot tables as opposed to ‘collapse all’/‘expand all’”?
Hello,
I think that is a very good suggestion. I will translate it to our Product team to be reviewed. Thank you so much for your help to make QuickSight better.
1 Like