How do I calculate a margin$ field in pivot table?

Question

How do I calculate a margin$ field in pivot table?

oliver fortes 0

I have two pivot tables, one contains units sold and unit price, the other contains the margin% agreed with a supplier.

User's image

I'm trying to create a calculated MARGIN$ column in the top table, but I can't seem to write the formula correctly.

I know it's basically Unit Price * Negotiated Price, but every time I think I've written it correctly, I get this:

User's image

Any help would be most appreciated, been sat staring at this for hours now.

oliver fortes 0 Reputation points

2025-11-25T13:21:14.8166667+00:00

These are how my relationships have been set up:
Ashish Mathur 100.8K Reputation points Volunteer Moderator

2025-11-25T23:29:56.9866667+00:00

Hi,

There is no common column between the 2 tables. How will any lookup work?

1 answer

Your answer

oliver fortes 0 Reputation points

2025-11-25T13:21:14.8166667+00:00

These are how my relationships have been set up:
Ashish Mathur 100.8K Reputation points Volunteer Moderator

2025-11-25T23:29:56.9866667+00:00

Hi,

There is no common column between the 2 tables. How will any lookup work?

Answer 1

Francisco Montilla 23,935 Independent Advisor

Hello,

You are getting that error because the calculated column is trying to read a value from another table without telling DAX which single row to use. For Pivot use a measure instead and pull the margin from the related table with RELATED.

In Power Pivot, in table Fact_Sales, create a new measure and paste this:

Margin$ := SUMX(Fact_Sales, Fact_Sales[Units Sold] * Fact_Sales[UnitPrice] * RELATED(Dim_Suppliers[Negotiated Margin]))

Add this measure to your Pivot. It multiplies Units Sold by UnitPrice by the negotiated margin that is reached through the relationships Fact_Sales > Dim_Products > Dim_Suppliers. If Dim_Suppliers has more than one row per product category, make it one row per category or change RELATED to an explicit aggregator such as MAX to force a single value.

oliver fortes 0 Reputation points

2025-11-25T14:03:28.7233333+00:00

I have done this but it returns this:

Also, in this instance I'm required to create a column, it's part of a learning task.
Francisco Montilla 23,935 Reputation points Independent Advisor

2025-11-26T06:09:46.93+00:00
You are getting blank because Fact_Sales has no direct relationship to Dim_Suppliers and RELATED cannot hop two tables. For the learning task create a staging column in Dim_Products, then use it from Fact_Sales.

In Power Pivot, in Dim_Products add a calculated column:

Negotiated Margin := RELATED( Dim_Suppliers[Negotiated Margin] )

Now in Fact_Sales add the required calculated column:

Margin$ := Fact_Sales[Units Sold] * Fact_Sales[UnitPrice] * RELATED( Dim_Products[Negotiated Margin] )

Add that Margin$ column to your Pivot. If you still see an error, check that Dim_Suppliers has exactly one row per Cat so that RELATED can return a single value.

Share via

How do I calculate a margin$ field in pivot table?

1 answer

Your answer