Chapter 2 of Analyzing data With Microsoft Power BI and Power Pivot for Excel discuss the use of header/details tables in data modeling. Affiliate link to the book.
The scenario of header/detail tables appears when you have two fact tables with similar information, but with different granularity. Each of the fact tables has its own dimensions. It looks like two star schemas linked together by a relationship between the facts tables.
The problem with this set-up is that, even though both tables are related to each other, filtering from a dimension belonging to one fact table will not work for metrics in the other fact table.
To solve this, you can either change your filtering or change your model.
Fast forwarding …. you should change your datamodel!
If you set up a bidirectional filtering between the two tables, part of your calculations will follow through, but not all.
If you adjust the granularity in one of your fact tables so it will match the ganularity of the other fact table, you are getting somewhere. Now you are changing your datamodel.
You have two fact tables with similar data, but with different level of details:
- Contracts (= header table)
- Contractdetails (= detail table)
Both of these fact tables has their own dimensions.
The header table has information per contract. For example contract value, type of contract, customer, discount and other terms.
Contractdetails have additionally information for example which products are included in each of the contracts, price and quantity.
By selecting a metric in your header-data (discount) and filtering a dimension linked to your detail-table (product), you will get incorrect measures because there is no relationship between the two. This is no good!
If you try to set up a bidirectional filter on the relationships between your facts tables, your calculations are not as far off as in the previous section, but they may still be incorrect.
One product can be part of several contracts with different discounts.
To solve this, you can adjust the metric you are working with, contract-discount, into the level of details you have in your detail table. This way you link the discount to your products. You denormalize your “discount” (denormalize = flatten).
“How?” You ask…
No need for bi-directional filtering in this example (but it will work regardless).
In your detail table, you can create a calculated column where you pick up the discount % from the headertable (through the RELATED function) and you multiply this % with price * volume that already exists in your detailtable. You now have a new column with discountvalues with the same granularity as all your detailsdata.
= RELATED(Contracts[discount]) * price * volume
Now you can summerize and filter as much as you would like.
If you now denormalize your remainder header table into your details table in the same manner, you will be left a perfect star schema and the issue is resolved 👊
I have found that context is an important topic to understand. It is complicated. I have re-read part of this book several times and I still realize that there is more depth there than I thought during my first read-through.
So you there — what are your thoughts? Have you read the book?
Is this straight forward? Have you used this technique to solve the header / details issue?