This topic contains 1 reply, has 2 voices, and was last updated by 
-
Posts
-
I would like to provide users a way to dynamically select the dimension by which they analyse data in a pivot table. In a simple example (attached), my fact table has 3 columns: product type, colour, volume. The pivot table consolidates the volume, and a slicer allows the user to display the sum of volume either by product type or by colour.
The solution I have come to so far involves creating a lookup table with a single column that contains all the unique values of product type and colour. There are 2 relationships between the fact table and this lookup table: one active on the product type column, one inactive on the colour column.
A 3rd disconnected table provides a way to select which of the 2 relationships is the active one for the consolidation metric.
Would anyone have a different solution to this problem?
Interesting idea that effectively combines two pivot tables into one.
In a way, by combining the two pivot tables into one, you are choosing to answer the questions, “What are our highest volume products?” and “What are our highest volume colors?” separately from each other, where you might find that some products (or product categories) sell in greater volume with a given color and other products (or product categories) sell in greater volume in a different color.
Find out from users or your team leader which pivot configuration would be most useful for analysis.
The forum ‘Power Pivot’ is closed to new topics and replies.