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

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

jaryszek — Tue, 29 Jul 2025 09:02:31 GMT

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-transactions/

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!

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

jaineshp — Tue, 29 Jul 2025 12:24:31 GMT

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.

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

jaryszek — Tue, 29 Jul 2025 13:40:04 GMT

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

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

jaineshp — Wed, 30 Jul 2025 04:14:31 GMT

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

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

jaryszek — Wed, 30 Jul 2025 07:07:21 GMT

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

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

v-veshwara-msft — Wed, 06 Aug 2025 07:39:47 GMT

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.

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

jaineshp — Thu, 07 Aug 2025 04:29:55 GMT

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