How to model and aggregate rate (dimension) and amount (fact) per Kimball “header/line item” ?

[jaryszek]

Hello,

I am working on a Power BI model for cloud billing (Azure Cost Management) and trying to follow the guidance from Kimball Group’s “header/line item transaction” modeling patterns:
https://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-t...

My setup:

• Fact table: Fct_EA_AmortizedCosts
  (Contains transaction/usage records, measures like TotalAmortizedCost)

• Dimension table: Dim_EA_AmortizedCosts_DiscountPlans --> will be converted to Fct_EA_AmortizedCosts_DiscountPlans
  (Contains rates/prices, like DiscountPlanEffectivePrice, and fields for topology: OfferId, ResourceLocationNormalized, PricingModel) -> it is aggregated table created on top of Fct_EA_AmortizedCosts

• Both tables are/will be connected to the same Date dimension for analysis.

Goal:
- I want to build a matrix with:The three topology fields (OfferId, ResourceLocationNormalized, PricingModel)
-The “DiscountEffectivePrice” (from the newly created fact table)
- The “TotalAmortizedCost” (from the fact table)

How to achive it?

Any advice or best practice pointers are very welcome—especially for cloud billing and discount plan modeling!

Thank you!

[jaryszek]

Hello @jaineshp .

It could be nice but I afraid about having too many shared dim tables in a model. It will be difficult to handle. 
What about DAX statement? 
Could i handle it as measure? 

p.s. I know yur answer from chat gpt. But really is the solution from real life example? 

Best,
Jacek

[jaineshp]

Hi @jaryszek ,

Totally understand your concern, having too many shared dimensions in a model can make it feel heavy and harder to manage, especially if the fact tables keep growing.

DiscountEffectivePrice (Avg) :=
CALCULATE(
AVERAGE('Fct_EA_AmortizedCosts_DiscountPlans'[DiscountEffectivePrice]),
TREATAS(
VALUES('Fct_EA_AmortizedCosts'[OfferId]),
'Fct_EA_AmortizedCosts_DiscountPlans'[OfferId]
),
TREATAS(
VALUES('Fct_EA_AmortizedCosts'[ResourceLocationNormalized]),
'Fct_EA_AmortizedCosts_DiscountPlans'[ResourceLocationNormalized]
),
TREATAS(
VALUES('Fct_EA_AmortizedCosts'[PricingModel]),
'Fct_EA_AmortizedCosts_DiscountPlans'[PricingModel]
)
)

Hope this helps!

Best Regards,
Jainesh Poojara | Power BI Developer

[jaryszek]

Thanks. 

I do not know if Treatas is the best option here. Also chat gpt advised this to me. 
Let's wait for more optimized daxes. 

Best,
Jacek

[v-veshwara-msft · Accepted solution]

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 Community

Solved: 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.

[jaineshp]

Hey @v-veshwara-msft,

Thank you for the kind recognition - always happy to contribute to our community's success!

Best Regards,
Jainesh Poojara | Power BI Developer

[jaineshp]

Hi @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.