How to sum a rate from a dimension table only for the keys present in my fact table?

[jaryszek]

Hi Power BI Community,

I have a classic star schema model with a fact table and a dimension table. My dimension table includes a numeric rate/price field (DiscountPlanEffectivePrice) and a key called DateDiscountPlanMeterId. This key is also present in my fact table, and it's used for joining the two tables.

In my matrix visual, I’m grouping by various topology fields (like region, offer, pricing model, etc.) from my fact table, and I want to display the sum of the unique DiscountPlanEffectivePrice values from the dimension table, but only for the keys that actually exist in the current filter context of my fact table.

My goal is to:

Avoid summing all rates from the dimension, and

Avoid duplicating the same rate for every related fact row.

I’ve tried DAX measures like:

Sum of Effective Discount Prices =
SUMX (
    VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]),
    CALCULATE(
      SELECTEDVALUE(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice])
    )
)

and also attempted using SUMMARIZE, but I’m either getting blank results or incorrect totals.

The relationship between the tables is active and based on DateDiscountPlanMeterId.

How can I correctly sum only the unique rates from the dimension for keys that are present in my fact table’s current context?
Is there a better DAX pattern, or am I missing something in the way context or relationships should be handled for this scenario?

Any suggestions or guidance would be greatly appreciated!

Thanks in advance!
Jacek

[jaineshp]

Hi @jaryszek,

Great question — this is a common scenario when working with star schemas in Power BI.

You're on the right track with wanting to sum only the unique DiscountPlanEffectivePrice values from your dimension table (Dim_EA_AmortizedCosts_DiscountPlans) — and only for the DateDiscountPlanMeterId values that are present in the current filter context of your fact table (Fct_EA_AmortizedCosts).

The challenge arises because using a simple SUM or even SUMX can inadvertently aggregate more than you intend — either repeating prices for each fact row, or including dimension rows that aren’t relevant in the current visual.

Recommended Approach

Here’s a robust DAX measure pattern to achieve your goal:

Sum of Effective Discount Prices :=
SUMX(
VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]),
CALCULATE(
MAX(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice])
)
)

• VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]):
  This gets the distinct keys from the fact table within the current visual/filter context. This ensures you’re working only with what's visible or filtered.

• CALCULATE(MAX(...)):
  Inside SUMX, we retrieve the single DiscountPlanEffectivePrice from the dimension table per key.

  • If each key maps to a unique price (as expected in your scenario), MAX works fine and avoids errors from SELECTEDVALUE when there are unexpected duplicates.

• SUMX(...):
  Sums only those prices associated with keys that exist in the filtered fact table — no duplication or unwanted dimension rows.

Optional: Use SELECTEDVALUE if Confident in Data Uniqueness

If you're certain that each DateDiscountPlanMeterId has exactly one corresponding price in the dimension table, you can also use:

Sum of Effective Discount Prices :=
SUMX(
VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]),
CALCULATE(
SELECTEDVALUE(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice])
)
)

Hope this helps! Hit that like button and mark it as solution.

Best Regards,
Jainesh Poojara | Power BI Developer

[v-ssriganesh]

Hello @jaryszek,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI Desktop using a sample dataset based on the details you provided. I was able to achieve the expected output as per your requirement.

I used the following DAX measure to address your need:

Sum of Effective Discount Prices =

CALCULATE(

    SUMX(

        DISTINCT('Dim_EA_AmortizedCosts_DiscountPlans'[DateDiscountPlanMeterId]),

        CALCULATE(

            MAX('Dim_EA_AmortizedCosts_DiscountPlans'[DiscountPlanEffectivePrice])

        )
    ),

    FILTER(

        'Dim_EA_AmortizedCosts_DiscountPlans',

        'Dim_EA_AmortizedCosts_DiscountPlans'[DateDiscountPlanMeterId] IN VALUES('Fct_EA_AmortizedCosts'[DateDiscountPlanMeterId])

    )
)

This measure ensures that only unique rates for keys present in the fact table are summed, avoiding duplication and including only the relevant context.

Expected Output:

This matches your goal of avoiding summation of all rates and preventing duplication. For your reference, I have attached a .pbix file containing the reproduced scenario and the working solution. You can download it, explore the model, and adjust it with your actual data.

If you have any further questions, please don't hesitate to contact us through the community. We are happy to assist you.

Best Regards,
Ganesh singamshetty.

[johnt75 · Accepted solution]

You could try

Sum of Effective Discount Prices =
SUMX (
    SUMMARIZE (
        Fct_EA_AmortizedCosts,
        Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice]
    ),
    Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice]
)

[Azadsingh]

Hi @jaryszek , Please try below DAX  and share your inputs:

Sum of Unique Discount Prices =
SUMX(
VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]),
RELATED(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice])
)

Regards,

Azad

[jaryszek]

Thanks. 

This is not working: 

Best,
Jacek

[Azadsingh]

Hi @jaryszek , Please fix the column name in the error or try this 

Sum of Effective Discount Prices =SUMX (VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]),CALCULATE(
SELECTEDVALUE(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice])))

[jaryszek]

thanks, selected value will not work. I need a ful;l row-context