Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello again,
I'm building upon an initial solution bystalwar1 that worked great untill now, but as business request changed, I'm having trouble changing the model to include this new requests.
The question I'm trying to answer, using a Chart (Straight or Pivot Table) layout, is the following:
1. Considering that we have a PRODUCT table where:
- one Brand has more than one Product
- one Product has more than one Pack,
- each Pack can be in more than one Sub-Markets (or Categories)
2. Considering that we have one FACT table where our measure is SALES_UNITS and our dimension are Pack, Store and a SALES_CONTRIBUTION (being a binary qualifying if the line is relevant for SALES calculations or not).
We want to know for each Product (or Brand, or Pack) on the Pivot table, what is the total Sales Units on the Product (dimension) respective Sub-Markets, but only on the stores that have sold (that have SALES_CONTRIBUTION =1) the product.
Something like this:
Product (dimension) | Sales Units (expression) |
Prod_A | Total Sales_Units of the Prod_A's Sub-Markets, on the Stores that sold Prod_A |
Prod_B | Total Sales_Units of the Prod_B's Sub-Markets, on the Stores that sold Prod_B |
Prod_C | … |
The general idea is to change dimensions dynamically on user selection, so the data model has to be flexible and dynamic, and preferably to use the same expression for all dimension levels.
Please see attached problem model.
Thank for your help.
Best Regards
Pedro Freire
