Issue calculating "Total Price" in Power BI using DAX

[DhanashreeP]

Hello,
I am trying to calculate Total Price in my Power BI report.

• Requirement: The "Total Price" should be the sum of all "Price" values for each respective "Type".
• Data Model Details: The table is connected to the Fact table through a many-to-many (M2M) relationship, because it does not have a unique primary key.

I tried writing a DAX formula to calculate "Total Price", but instead of showing the expected totals, it is returning inflated/very large numbers.
How should I correctly calculate the "Total Price" in this scenario, considering the many-to-many relationship?

[v-sshirivolu]

Hi @DhanashreeP ,
I would also take a moment to thank @tayloramy  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[v-sshirivolu]

Hi @DhanashreeP ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @DhanashreeP ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file. 

[Royel]

Hi @DhanashreeP  You can achieve this by creating matrix visual 

Rows: Type, Product ID

Values: Total Price

Dax: 

Total Price = 
   IF(
       ISINSCOPE('Data'[Product ID]),
       BLANK(),
       SUM('Data'[Price])
   )

Thanks 

[Shahid12523]

Use this

Total Price =
CALCULATE(
SUMX(
DISTINCT('YourTable'[Product ID]),
RELATED('YourTable'[Price])
)
)

[tayloramy · Accepted solution]

Hi @DhanashreeP,

When your “price list” table participates in a many-to-many relationship, filters can propagate in both directions and effectively multiply rows (a mini Cartesian join). A simple SUM( Price ) then counts the same product price multiple times, so totals look inflated. The two clean ways to solve it are: (1) fix the model (preferred), or (2) write a measure that sums prices only from the price table while ignoring the M2M join.

Best fix (model): Create a proper dimension with unique keys and use single-direction filters.

• Make DimProduct (or at least DimType) with unique values.
• Relate DimProduct[Product ID] → PriceList[Product ID] (one-to-many, single direction).
• Then the measure is trivial:

Total Price = SUM( PriceList[Price] ) 

Docs: Many-to-many relationships, Star schema guidance.

If you can’t change the model right now: Neutralize the M2M in the measure so you only sum once per product.

Total Price (safe) =
CALCULATE(
SUM( PriceList[Price] ),
REMOVEFILTERS( Fact ), -- ignore fact table filters
CROSSFILTER( PriceList[JoinKey], Fact[JoinKey], NONE ) -- break the M2M for this calc
)

• Replace JoinKey with the column(s) used in the relationship. If there are two join columns, add a CROSSFILTER for each.

Docs: CROSSFILTER, REMOVEFILTERS.

If the price list can have duplicate Product IDs: Sum once per product to avoid duplicates.

Total Price (per product, de-duplicated) =
SUMX(
VALUES( PriceList[Product ID] ),
CALCULATE( MAX( PriceList[Price] ), REMOVEFILTERS( Fact ) )
)

Put Type on rows, use the measure above, and you’ll get the correct totals 612, 2534, 11000 for your example.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.