Forum Discussion
Super UserHow to model and aggregate rate (dimension) and amount (fact) per Kimball “header/line item” ?
Hi jaryszek ,
Thanks for the follow up. Understood that you're exploring whether TREATAS is the best fit here and waiting for more optimized DAX inputs.
Just to add some context - TREATAS is commonly used in scenarios involving multiple fact tables at different grains. It helps propagate filters between them without needing physical relationships, which can be beneficial for both performance and flexibility in complex models. This is particularly relevant when dealing with rate-based vs transactional fact tables.
You might find these references useful:
Solved: Best practice for structuring data with Header and... - Microsoft Fabric CommunitySolved: How to create measures across multiple fact tables... - Microsoft Fabric Community
Solved: Re: TREATAS to filter multiple columns in combinat... - Microsoft Fabric Community
Thanks to jaineshp for valuable suggestions.
Hope this helps. Please reach out for further assistance.
Thank you.
Memorable MemberHi jaryszek,
To model and aggregate both rates (like DiscountEffectivePrice) and amounts (like TotalAmortizedCost) following Kimball’s “header/line item” approach, especially in a Power BI context for cloud billing, consider the following structure and best practices:
1. Separate the Two Fact Tables by Grain
Fct_EA_AmortizedCosts: Represents detailed line item-level transactions, such as usage and cost at the most granular level.
Fct_EA_AmortizedCosts_DiscountPlans: Represents rate-level data, such as DiscountEffectivePrice, typically aggregated by a pricing dimension grain (e.g., OfferId, PricingModel, ResourceLocationNormalized).
Do not treat DiscountEffectivePrice as a dimension attribute if it varies by usage context—model it as a separate fact.
2. Create a Common Dimensional Topology
Define a conformed dimension table (e.g., Dim_DiscountPlanTopology) with:
OfferId
ResourceLocationNormalized
PricingModel
Both fact tables should join to this dimension. This enables slicing across both facts using a consistent dimensional structure.
3. Use Role-Playing or Shared Date Dimension
Ensure both fact tables use the same Dim_Date dimension so time-based aggregation aligns correctly in the model.
4. Build the Matrix in Power BI
In Power BI:
Use a matrix visual with rows from the shared dimension (OfferId, ResourceLocationNormalized, PricingModel).
Add TotalAmortizedCost from Fct_EA_AmortizedCosts as one measure.
Add DiscountEffectivePrice from Fct_EA_AmortizedCosts_DiscountPlans as another measure.
Ensure appropriate relationships exist between:
Fact tables and Dim_DiscountPlanTopology
Fact tables and Dim_Date
5. Handling Aggregation Logic
TotalAmortizedCost: Use SUM as it’s additive.
DiscountEffectivePrice: Use MIN, MAX, or AVG depending on the business requirement. If there’s one effective price per combination, use MAX or FIRSTNONBLANK.
6. Avoid Pitfalls
Do not try to model rates in the same table as amounts if their grains differ.
Do not use direct relationships between the two fact tables. Let dimensions serve as the bridge.